Program to run through functions in a single button (1 function = 1 click)

user_7228703 0
Viewed 114

I am trying to code a button that runs multiple functions as you click it each time. I was thinking of running a loop but can't seem to find a suitable loop syntax in the library.

This is what I think it should look like (have not touched VBA Excel in some months)

Private Sub CommandButton15_Click() 
    i As Click
    r As Counter

    i = CommandButton15.Click

    For Each i In UserForm1    
        If r = 1 Then
            TextBox1.Text = "Do 1st function"
        ElseIf r = 2 Then
            TextBox1.Text = "Do 2nd function"
        Else
            TextBox1.Text = "Do 3rd function"
        End If
    Next i
End Sub

The code and buttons/textboxes is in a Userform that comes up. Appreciate if anyone could help to figure out the syntax or an example.

Thanks!

button vba excel
3 Answers

In your Userform, store a global variable and increase the counter in the Click event of your button.

Option Explicit

Private clickCount As Long

Private Sub CommandButton15_Click()
        
    clickCount = clickCount + 1
    
    Select Case clickCount
        Case 1
            TextBox1.Text = "Do 1st function"
        Case 2
            TextBox1.Text = "Do 2nd function"
        Case Else
            TextBox1.Text = "Do 3rd function"
    End Select
End Sub
user_155979360

From my point of view, you have two easy options:

  • If you are using your code within a sheet, you can store a 'currentStep' flag in a hidden cell. (Maybe the last row, last column, one)
  • If you don't have a sheet, or you don't what to use it, maybe you can create a simple txt file, store your flag there, and load it evey time you run your code.

In both cases, after reading the 'currentStep' value, you can the decide which part of code to run.

user_156286360

appreciate all your answers!

I haven't got to trying them yet but the good news is that eventually I've found the answer through a simple If-Else without a loop function.

Option Explicit
Public i As Integer
Private Sub CommandButton15_Click()

    If i = 0 Then
        TextBox1.Text = "Do 1st function"
        i = 1
    ElseIf i = 1 Then
        TextBox1.Text = "Do 2nd function"
        i = 2
    Else
        TextBox1.Text = "Do 3rd function"
        i = 0
    End If


End Sub

variable i have to be global though, not sure the reason as it gives a "Type Block" error if its a local variable.

user_72287030
Related