Can a VBA control reference itself in its own code? 1

[EdwardMartinIII]

I have a Word 2007 doc with a lot of status buttons on it. Hundreds, in fact.

For each button, this is the code I plan to have:

Private Sub CommandButton1_Click()
  With CommandButton1
    Select Case .BackColor
      Case Is = &HFF&
        ' Button is red and should be switched to orange.
          .BackColor = &H80FF&
        ' Text should be changed from "High" to "Medium"
          .Caption = "Medium"
      Case Is = &H80FF&
        ' Button is orange and should be switched to yellow.
          .BackColor = &HFFFF&
        ' Text should be changed from "Medium" to "Low"
          .Caption = "Low"
      Case Is = &HFFFF&
        ' Button is yellow and should be switched to green.
          .BackColor = &HFF00&
        ' Text should be changed from "Low" to "None"
          .Caption = "None"
      Case Is = &HFF00&
        ' Button is green and should be switched to gray.
          .BackColor = &HC0C0C0
        ' Text should be changed from "None" to "N/A"
          .Caption = "N/A"
      Case Is = &HC0C0C0
        ' Button is gray and should be switched to red.
          .BackColor = &HFF&
        ' Text should be changed from "None" to "High"
          .Caption = "High"
    End Select
  End With
End Sub

The same action happens for each button.

Obviously, this is a lot of copy-and-pasting, and I'd have to change a line in each procedure.

Is there a simpler way to do this?

For example, is there a way for each procedure to have a line that reads:

Private Sub CommandButton1_Click()
  
  With TheControlClickedToCallThis [b]<--is there something like this?[/b]
    Select Case .BackColor
      Case Is = &HFF&
        ' Button is red and should be switched to orange.
          .BackColor = &H80FF&
        ' Text should be changed from "High" to "Medium"

...(and so forth)

Another example:

Is it possible to build a single procedure, such as this:

Private Toggle_The Switch()
  With TheControlClickedToCallThis
    Select Case .BackColor
      Case Is = &HFF&
        ' Button is red and should be switched to orange.
          .BackColor = &H80FF&
        ' Text should be changed from "High" to "Medium"
          .Caption = "Medium"
      Case Is = &H80FF&
        ' Button is orange and should be switched to yellow.
          .BackColor = &HFFFF&
        ' Text should be changed from "Medium" to "Low"
          .Caption = "Low"
      Case Is = &HFFFF&
        ' Button is yellow and should be switched to green.
          .BackColor = &HFF00&
        ' Text should be changed from "Low" to "None"
          .Caption = "None"
      Case Is = &HFF00&
        ' Button is green and should be switched to gray.
          .BackColor = &HC0C0C0
        ' Text should be changed from "None" to "N/A"
          .Caption = "N/A"
      Case Is = &HC0C0C0
        ' Button is gray and should be switched to red.
          .BackColor = &HFF&
        ' Text should be changed from "None" to "High"
          .Caption = "High"
    End Select
  End With
End Sub

and having that procedure called by each button click like this:

Private Sub CommandButton1_Click()
  Toggle_The Switch
End Sub

I'm open to suggestions.

I'm not against coding each button individually, but if there was a slicker way, I'd really like to do that instead.

Thanks!


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[Andrzejek]

Is it really "a Word 2007 doc with a lot of status buttons on it" or is it a UserForm in Word 2007 "with a lot of status buttons on it" (on the UserForm)?

Have fun.

---- Andy

 

[TonyJollans]

Something like this ...

[blue]Private Sub CommandButton1_Click()
    Colour Me.CommandButton1
End Sub
Private Sub CommandButton2_Click()
    Colour Me.CommandButton2
End Sub
Private Sub CommandButton3_Click()
    Colour Me.CommandButton3
End Sub
Private Sub Colour(MyCommandButton As CommandButton)
 With MyCommandButton
    Select Case .BackColor
      Case Is = &HFF&
        [green]' Button is red and should be switched to orange.[/green]
          .BackColor = &H80FF&
        [green]' Text should be changed from "High" to "Medium"[/green]
          .Caption = "Medium"
      Case Is = &H80FF&
        [green]' Button is orange and should be switched to yellow.[/green]
          .BackColor = &HFFFF&
        [green]' Text should be changed from "Medium" to "Low"[/green]
          .Caption = "Low"
      Case Is = &HFFFF&
        [green]' Button is yellow and should be switched to green.[/green]
          .BackColor = &HFF00&
        [green]' Text should be changed from "Low" to "None"[/green]
          .Caption = "None"
      Case Is = &HFF00&
        [green]' Button is green and should be switched to gray.[/green]
          .BackColor = &HC0C0C0
        [green]' Text should be changed from "None" to "N/A"[/green]
          .Caption = "N/A"
      Case Is = &HC0C0C0
        [green]' Button is gray and should be switched to red.[/green]
          .BackColor = &HFF&
        [green]' Text should be changed from "None" to "High"[/green]
          .Caption = "High"
    End Select
  End With
End Sub
[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[EdwardMartinIII]

It's a Word Doc. It has several large tables. In the various table cells are these buttons.

It is not a separate Userform.


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

Tony, that worked perfectly! Man, this sort of issue's been haunting me for a couple of projects.

Thank you!

Cheers,


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door