Command button depending on Cell Value

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi

I'm trying to have a command button which works differently depending
on the value in a cell (V3). If the value is 2, I want the button text
to say 'Go to Part 5' and then go to sheet 5 when clicked, or if the
V3 value is 1, I want the button to say 'Go to Part 9' and go to
worksheet 9 when clicked.

Is this possible?

I'd also tried doing it as 2 buttons, one over the other, with the
second being invisible unless the value of V3 = 2.

Would this be easier, and if so, how would I do it?

I'd really be grateful for any advice.

Steve
 
Hi Steve,

You do need two event macros ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$V$3" Then
With Target
If .Value = 1 Then
CommandButton1.Caption = "GO TO PART 5"
ElseIf .Value = 2 Then
CommandButton1.Caption = "GO TO PART 9"
End If
End With
End If
End Sub

Private Sub CommandButton1_Click()
If Range("V3").Value = 1 Then
Sheet5.Activate
ElseIf Range("V3").Value = 2 Then
Sheet9.Activate
End If
End Sub

HTH
 
Back
Top