Trigger CommandButton1 using code. Can I??? Help PLEASE!

[Mister H]

Hi All:

Can a Command Button be triggered using code? I know how to trigger other macros ( Application.Run "MacroNameHere" ) but I am not sure if the same thibng can be done for Command Buttons. Can it?

I want my macro to trigger CommandButton1...

Any assistance would be appreciated. I did a quick search but I must not be using the correct key words.

THANKS,
Mark

 

[Seti]

I am sure that there are other ways to do this that are more efficient, but how about having your command button call a macro, the same one that you want to call through code?

 

[Mister H]

Can't get that to work for me

Hi Seti:

THANKS for the reply. I can't get your solution to work. I think the reason is the fact that this button actually changes name after it is clicked. I got this code from a buddy and it eliminates the need for two buttons. Anyway, I just tried putting it as a macro but I get an error "Object Missing". Here is the code in case anyone has a use for it.

Does anyone know how I can get this to fucntion for me? THANKS.

Private Sub CommandButton1_Click()
'''''''''''''''''''''''''''''''''''''

' Hide_Zero_Rows Macro


Dim tmprow As Integer
Dim tmpsel As String
Dim TFAction As Boolean

ActiveSheet.Unprotect "unlock"
Application.ScreenUpdating = False


TFAction = True
If CommandButton1.Caption = "HIDE Blank Rows" Then
    TFAction = True
    CommandButton1.Caption = "SHOW Blank Rows"
Else
    TFAction = False
    CommandButton1.Caption = "HIDE Blank Rows"
End If

tmprow = 16
Do While Cells(tmprow, 1) <> "Bottom"
    tmprow = tmprow + 1
Loop


tmprow = tmprow - 1

Do
   
   If (Cells(tmprow, 1) = "" Or 0 And Cells(tmprow, 8) = "" Or 0) Then '1 is Column A and 14 is Column N
        tmpsel = tmprow & ":" & tmprow
        Range(tmpsel).EntireRow.Hidden = TFAction
 
    End If
    tmprow = tmprow - 1
Loop Until tmprow = 16

Application.ScreenUpdating = True
ActiveSheet.Protect "unlock"
End Sub

Take Care,
Mark

 

[Mister H]

Hi All:

Nobody had an answer for me which is a little unusual for this Board

I went and did a little Googling and then wound up getting this answer from Chip Pearson (THANKS Chip):

Change the declaration of the Click procedure from "Private" to "Friend" and then call the Click function as a normal procedure.

Sheet1.CommandButton1_Click	' if the button is on the worksheet

OR

UserForm1.CommandButton1_Click ' if the button is on a userform

Again THANKS to Chip for this info...
[url]www.cpearson.com
[/url]

Have a GREAT day ALL.

 

[Norie]

Mark

Glad you found a solution.

I think part of the reason you didn't get many replies is because it doesn't really seem logical, to me anyway, to use code to call an event.

Not saying it can't be done though, and you seemed to have it sorted.

By the way as far as I can see the button isn't actually changing name in that code, it's changing it's caption.

 

[TTom]

Sorry I missed reading this post.
It is a bit unusual as Norie says, but I did this exact call in code the other day. Glad you found solution. :wink: TT