Assigning macro to button

[Zak]

I have tried many times to add my macro to a button but it does not work. I
have tried both the control toolbox and the forms menu.

I am in design view when i create the button, double click it to show the
VBE, enter the below code, go back to the button coming out of design view
but nothing happens! not even an error. same when i do it via the forms menu,
i assign the button but nothing happens!!
(replace is my macros name.)

Private Sub CommandButton1_Click()
Run "replace"

End Sub

please help. thanks.

 

[Don Guillett]

On the forms (or a shape from the drawing menu)>right click the lower
right>when you see assign macro>just highlight the macro in the vbe

 

[FSt1]

I have tried many times to add my macro to a button but it does not work. I
have tried both the control toolbox and the forms menu.

I am in design view when i create the button, double click it to show the
VBE, enter the below code, go back to the button coming out of design view
but nothing happens! not even an error. same when i do it via the forms menu,
i assign the button but nothing happens!!
(replace is my macros name.)

Private Sub CommandButton1_Click()
Run "replace"

End Sub

please help. thanks.

 

[FSt1]

hi
sorry about the other post. hit the wrong button.
try this....

Private Sub CommandButton1_Click()
Call replace
End Sub

regards
FSt1

 

[Zak]

Hi,

thanks this worked but the problem is it only works when i assign the macro
in the sheet with all the data. if i put the button in sheet 2, which is
empty and where i want the button to be it doesnt work! maybe i need to put
in a condition in the button code so that it works regardless of what sheet
its in. also i found because i put in a condition 'find GIS' (in upper case)
it only finds it if its in upper case, can i do it so the macro will find
this word regardless of the case its in?

thanks a lot.

 

[JLGWhiz]

See this site.

http://www.ozgrid.com/News/jan-2006.htm

Unfortunately, you only get one shot to use the assign macro dialog box when
you use the button from the forms toolbar. The button from the Controls
Toolbox allows much more flexibility.

 

[Zak]

I have used the control toolbox method aswell. how can i get my macro to work
from sheet 2/3/4? at the moment it only works in the sheet with the data in
it.

thanks.

 

[FSt1]

hi
not sure how you coded the condition. post your code.
and as a thought... you could assign your code to a custom icon. the only
draw back to that is that the sheet button follows the file. costom icons are
unique to the machine.

 

[Lazzzx]

Hi,
If you write 'call Sheet2.replace' instead of call recplace you will be able
to call code from a diffrent sheet. Sheet2 !)

By the way, you can place you macro in a module instead of in an empty
sheet.

regards,
Lazzzx

 

[Gord Dibben]

Zak

Add the line Option Compare Text at the top of the module above your Sub

This tells the code to ignore case.


Gord Dibben MS Excel MVP

 

[JLGWhiz]

If you put the button on a sheet, then the underlying code to the button, as
well as the button, will be attached to that particular sheet. However, you
can put code in the VBA module (Module1 in most cases) that can be called by
the button's code.

Example:

Code underlying button in sheet1.

Private Sub CommandButton1_Click()
sortSh2Data 'Macro name in VBA module
End Sub

Code in VBA module called by button on sheet1 to do task
in sheet2.

Sub sortSh2Data()
Worksheets(2).Range("B150").Sort Key:=Range("B1")
End Sub


Notice that in the VBA module code, that the Worksheet for the code to
execute on is specified (Worksheets(2)). That way there is no confusion as
to where action will take place. If you use Selection or ActiveSheet, you
have to be very careful that the sheet you want the code to execute on is
Selected or Active.

 

[JLGWhiz]

There is a lot of information on this site for beginners. My opinion is that
is about as basic as it gets in explaining VBA programming.

http://www.excel-vba.com/excel-vba-contents.htm