PDA

View Full Version : Excel GUI : Activate a button from macro



VishalkumarT
05-22-2007, 04:06 AM
Hello Friends

Thank you for your replies for my previous querries.

Could anybody please let me know how can I invoke a button from macro?

To be more specific, I have two buttons in my excel file and each of these buttons contain different macro codes.
Beginning with 1st button, if certain condition comes in its macro code then 2nd button should be activated. So then I can have further chance to run another macro from this 2nd (now activated) button and analyse the result in a better way.
Bydefault 2nd button should be deactivated, so nobody can begin using this button.

Thank you for your replied in advance.

Kind Regards
Vishalkumar

Bob Phillips
05-22-2007, 04:13 AM
An example



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) Then
If .Value > 0 Then
Me.Buttons("Button 1").Visible = True
Me.Buttons("Button 2").Visible = False
Else
Me.Buttons("Button 1").Visible = False
Me.Buttons("Button 2").Visible = True
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

Simon Lloyd
05-22-2007, 04:14 AM
You would probably be better off hiding the button! like this:

Option Explicit
Private Sub CommandButton1_Click()
Me.CommandButton1.Visible = False
MsgBox "New Button activated"
Me.CommandButton2.Visible = True
End Sub
Private Sub CommandButton2_Click()
Me.CommandButton2.Visible = False
MsgBox "New Button Activated"
Me.CommandButton1.Visible = True
End Sub

Simon Lloyd
05-22-2007, 04:15 AM
Ouch!, touche'

VishalkumarT
05-22-2007, 06:20 AM
Hi Xld
Hi Simon

Great help once again:)

Thanx
VT


P.S. by the way, why Ouch, Touche' :)

Bob Phillips
05-22-2007, 06:31 AM
Because we have this joke about giving the same answers, and stepping on ecah others toes. We did again here, so ouch for the toes, touche for the same answer.

VishalkumarT
05-22-2007, 06:35 AM
That s really funny:)
Thanx anyways.

Simon Lloyd
05-22-2007, 10:53 AM
Yes Bob, but as always i have to conceed the space to you as your solutions are fuller and styled with much more finesse!

Bob Phillips
05-22-2007, 11:19 AM
Aah! If you would stop being a lazy git and stop laying on your back, you might beat me (I can't get smilies)

Simon Lloyd
05-22-2007, 11:41 AM
With comments like that i'm not suprised you can't get smiles!

Lol ;)