Button text equals cell value

[cdulong]

I currently have a sheet that has a form button that a user can press to go to a administration sheet. I want to be able to change the text on the button dynamically.

If E6 = 11 I would like the text on the button to read "Admin"

If E6 <> 11 I would like the text on the button to read "Payperiod 1"

Is there any way that I can accomplish this with vba? And if so were about would I place the code.

Thanks,
cdulong

 

[SkipVought]

Hi,

Past this code in the Sheet Object (right-clickSheet tab , select View Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [E6]) Is Nothing Then
        Select Case Target.Value
            Case 11
                ActiveSheet.Shapes("Button 1").OLEFormat.Object.Text = "ADMIN"
            Case Else
                ActiveSheet.Shapes("Button 1").OLEFormat.Object.Text = "Payperiod 1"
        End Select
    End If
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...and assuming that the button name is Button 1.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[cdulong]

How do i know what the button name is? i have tried looking for this and not sure where to look for this...

 

[SkipVought]

when you right-click the button, the name is in the Name Box, adjacent to the Formula Bar.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[cdulong]

Thanks worked like a dream thanks!!