Excel VBA code to disable four buttons in order of priority

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.

1 answer

  • answered 2022-04-19 13:35 Attila

    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.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum