Excel VBA code to disable four buttons in order of priority

Question

There are four buttons to click to take four sample weights. The buttons are B1,B2,B3 and B4. B1 must be clicked first, followed by B2, then B3 and lastly B4 in that order of priority. I want to write an excel VBA code for below senario: (1)B2, B3 and B4 must be disabled until B1 is clicked and (2)B3 and B4 must be disabled until B2 is clicked and (3)B4 must be disabled until B3 is clicked

Any idea would help.

Answer 1

One approach is to enable/disable the button - you add 4 buttons, assign the event Button1_Click to Button4_Click and use Button1.Enabled - Button4.Enabled. Please note that Enabled does not turn off the events assigned to the button. Also .Enabled will not change the color, it's better to use your own handler for that. What I would do is to add the 4 buttons, leave their names as they are (Button 1 to Button 4). Then use this somewhat generic solution:

Option Explicit

Public Sub Button1_click()
    If Sheet1.Buttons(Application.Caller).enabled = False Then Exit Sub
    Dim btn As Button,arr As Variant,s%
    arr = Array(2, 3, 4, 1)
    s = Int(Split(Application.Caller, " ")(1)) - 1
    For Each btn In Sheet1.Buttons
        changeButtonStatus btn, btn.Name = "Button " & arr(s)
    Next btn
End Sub

Private Sub changeButtonStatus(ByVal btn As Button, ByVal enabled As Boolean)
    btn.Font.ColorIndex = IIf(enabled, 1, 15)
    btn.Enabled = enabled
End Sub

What does it do? Creates an array of integers which point to the next button to be enabled. First is Button 2, second is Button 3, third is Button 4, fourth is Button 1. Then checks which button was pressed (Application.Caller), and retrieves the number at the end (by using Split function), and decrease it by one - because the array indexing starts at 0. Then changes the status - if the button is the next one, it enables it, else disables.