Excel VBA - using a button in multiple sheets

  Heefie 12:46 27 Nov 2004

Me again ... as I said before, I'm VERY new to VBA so some of my questions may seem a bit basic or simple, but I'm doing my best !!!

I have a Spreadsheet with a number of Worksheets, let's call them AAA thru ZZZ. I have created a button in Worksheet AAA that links to & opens a program that uses files relevant to AAA. The Button is called AAAButton

Rather than duplicate this in BBB, CCC etc., etc., I moved the Code to ThisWorkbook(Code) from AAA(Code) & copied the button to all Worksheets.

I intended to write some code to pick up the WorkSheet name & use that in a generic link, but haven't got that far yet, as I wanted to make sure the Button would work like this.

If I step into the code & run it through, then the program opens the linked Program using the AAA details as originally intended and written. I assumed, therefore, that whichever sheet I was in, I could click on the Button (it is called AAAButton in all sheets) & it would open the program & the AAA files. I could then manipulate the code as I said above, to pick up BBB files from Worksheet BBB, CCC files from CCC etc., etc., etc.

However, clicking on ANY button now does absolutely nothing !!!

So, have I made a fundemental error in believing I could shortcut like this, or am I doing the most basic thing in the world wrongly (is that a real word ?!?!) ?!?!?!?

  VoG II 12:52 27 Nov 2004

Firstly I think that the code should go in an ordinary Module. In the VB Editor, Insert/Module and paste the code in there.

Right click one of the buttons, Assign Macro and choose your macro (probably the only one in the list). Right click the button again, select Copy. Then paste into each of the other sheets and delete the old buttons.

  Heefie 13:31 27 Nov 2004

... except I don't have an "Assign Macro" option when I right click !!!

I have Cut, Copy, Paste, Properties, View Code, CommandButtonObject, Grouping, Order & Format Control ...

  VoG II 13:39 27 Nov 2004

I suggest that you create a new button and try again. Sorry.

  Heefie 13:44 27 Nov 2004

... don't say sorry !!!

I'll try that now, I appreciate your help ... is there anywhere on the Web you would recommend for learning VB/VBA rather than just looking up solutions & scouring forums ?!?!?

  pc moron 13:48 27 Nov 2004

The button you're trying to use is, effectively, an embedded control- it's embedded into the appropriate worksheet.

If you right click the button and select View Code, a window will open and you'll see something like:

Private Sub CommandButton1_Click()

End Sub

You'll get one of these procedures for each sheet you put a control button on.

You could use the button on the Forms toolbar- you can assign a macro to this button.

The control (embedded) buttons are better when it comes to security etc. If it's just a simple app you're creating, you'll probably be better off with the Forms toolbar.

  VoG II 13:54 27 Nov 2004

click here is very good although you might find the first few chapters a bit wearing if you are an experienced programmer.

Others click here click here click here click here (music!) click here click here click here

  Heefie 15:22 27 Nov 2004

... Thanks for your help, again, I'm learning all the time but I suspect it could be a big & long learning curve for an old COBOL programmer <g> !!!

  Simsy 15:35 27 Nov 2004

(because it was for something I was doing that never got completed for spurios reasons), but an approach that might be appopriate for you is to have a macro run on opening the file that creates a toolbar containg the button. The button would have the vba code to perform the task, and would be operational at all times.

Another macro would run on closing to delete the toolbar.

I hope this helps!



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Add Depth Of Field to a photo using Tilt Shift Blur in Photoshop

iPhone tips & tricks

Comment afficher des fichiers cachés sur Mac ?