CODEWITHSUNDEEP
vbaexceluserform
Xavi
Xavi

Reputation: 207

Could I do without the commandbutton1 in my userform?

I have a vba code for a userform in Excel. This userform allows me to display a listbox which shows all the worksheets available... Then after selecting the desired worksheet in my listbox and by clicking in my userform one button called " CommandButton1", it selects me the desired worksheet... However I would like simply by selecting and clicking on my desired worksheet in my listbox, it selects me the desired worksheet( In this way, i would no need any more to click on my button " CommandButton1" in my userform to select my desired worksheet)...If someone could help me with that, it would be really wonderful.. Many thanks in advance.Xavi please find my code below:

Sub CommandButton1_Click()

    Worksheets(ListBox1.Value).Select
End Sub

Sub UserForm_Initialize()
    Dim n As Integer
    Dim msg As String

    On Error GoTo Exit
    Do
        n = n + 1
        ListBox1.AddItem Sheets(n).Name
    Loop Until n = Worksheets.Count

    If ListBox1.Value.Selected Then
        CommandButton1_Click = True
    Else
        CommandButton1_Click = False
    End If
    Exit:

End Sub

Upvotes: 0

Views: 60

Answers (1)

DisplayName
DisplayName

Reputation: 13386

all you need is ListBox Click event handler:

Option Explicit

Private Sub ListBox1_Click()
    With Me.ListBox1
        If .ListIndex <> -1 Then Worksheets(.Value).Select
    End With
End Sub



Sub UserForm_Initialize()
    Dim n As Integer

    Do
        n = n + 1
        ListBox1.AddItem Sheets(n).Name
    Loop Until n = Worksheets.Count

End Sub

BTW you UserForm_Initialize() can be a little simplified as follows

Sub UserForm_Initialize()
    Dim sht As Worksheet

    For Each sht In Worksheets
        ListBox1.AddItem sht.Name
    Next
End Sub

Upvotes: 1

Related Questions