Button to display text; each time it is hit, display next in row

[passedpawns]

Hello Everyone!

I have a list of ID's in cell A1 - A50 on 'Sheet 1'. I have a summary page on 'Sheet 2'. I have a button on Sheet 2 that I want to pull the value in A1. When it is hit again, it shows A2. Again, A3 etc.

Please let me know how this can be accomplished! Thank you!

 

[JoeMo]

When the value has been "pulled", where do you want it to show. When you click the button again, do you want the next value to overwrite the previous one? If not, where do you want subsequent values to show?

 

[passedpawns]

When the value has been "pulled", where do you want it to show. When you click the button again, do you want the next value to overwrite the previous one? If not, where do you want subsequent values to show?

When value is pulled, display on sheet 2, cell A1. When button is clicked again, the previous value can be overwritten with the new value.

 

[JoeMo]

The code below is for a form control button (not an active-x button). Place the button on Sheet2. The button caption will specify which ID# (from 1 - 50) will be shown in Sheet2 cell A1 on the next button click. Note: my button was button 3 (in red) - change it to natch your button's name.

Sub Button3_Click()
Static Ct As Long
Application.ScreenUpdating = False
Select Case Ct
    Case Is < 50
        Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A" & Ct + 1).Value
        Ct = Ct + 1
        If Ct < 50 Then
            With ActiveSheet.Shapes("Button 3")
                .Select
                Selection.Characters.Text = "Click for ID# " & Ct + 1
                Range("A1").Select
            End With
        Else
            With ActiveSheet.Shapes("Button 3")
                .Select
                Selection.Characters.Text = "Click for ID# " & 1
                Range("A1").Select
            End With
        End If
    Case Is >= 50
        Ct = 0
        Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A" & Ct + 1).Value
        Ct = Ct + 1
        With ActiveSheet.Shapes("Button 3")
            .Select
            Selection.Characters.Text = "Click for ID# " & 1
            Range("A1").Select
        End With
End Select
Application.ScreenUpdating = True
End Sub

 

[passedpawns]

The code below is for a form control button (not an active-x button). Place the button on Sheet2. The button caption will specify which ID# (from 1 - 50) will be shown in Sheet2 cell A1 on the next button click. Note: my button was button 3 (in red) - change it to natch your button's name.

Sub Button3_Click()
Static Ct As Long
Application.ScreenUpdating = False
Select Case Ct
    Case Is < 50
        Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A" & Ct + 1).Value
        Ct = Ct + 1
        If Ct < 50 Then
            With ActiveSheet.Shapes("Button 3")
                .Select
                Selection.Characters.Text = "Click for ID# " & Ct + 1
                Range("A1").Select
            End With
        Else
            With ActiveSheet.Shapes("Button 3")
                .Select
                Selection.Characters.Text = "Click for ID# " & 1
                Range("A1").Select
            End With
        End If
    Case Is >= 50
        Ct = 0
        Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A" & Ct + 1).Value
        Ct = Ct + 1
        With ActiveSheet.Shapes("Button 3")
            .Select
            Selection.Characters.Text = "Click for ID# " & 1
            Range("A1").Select
        End With
End Select
Application.ScreenUpdating = True
End Sub

Thank you!!!

 

[JoeMo]

Thank you!!!

You are welcome - thanks for the reply.

 

[V_Malkoti]

Joe's code with Mod operator:

Sub Button3_Click()
    Static Ct As Long
    Application.ScreenUpdating = False
    
    Sheets("Sheet2").Range("D1").Value = Sheets("Sheet1").Range("A" & Ct + 1).Value
    Ct = (Ct Mod 50) + 1
    If Ct = 50 Then Ct = 0
    Debug.Print "Ct=" & Ct
    With ActiveSheet.Shapes("Button 3")
        .Select
        Selection.Characters.Text = "Click for ID# " & Ct + 1
        Range("A1").Select
    End With
    
    Application.ScreenUpdating = True

End Sub

 

[passedpawns]

What does the MOD operator do?

 

[V_Malkoti]

Mod operator divides two numbers and returns the remainder. It is quite useful in creating cyclical sequences. More info here.

 

[JoeMo]

Joe's code with Mod operator:

Sub Button3_Click()
    Static Ct As Long
    Application.ScreenUpdating = False
    
    Sheets("Sheet2").Range("D1").Value = Sheets("Sheet1").Range("A" & Ct + 1).Value
    Ct = (Ct Mod 50) + 1
    If Ct = 50 Then Ct = 0
    Debug.Print "Ct=" & Ct
    With ActiveSheet.Shapes("Button 3")
        .Select
        Selection.Characters.Text = "Click for ID# " & Ct + 1
        Range("A1").Select
    End With
    
    Application.ScreenUpdating = True

End Sub

Nice simplification!