CODEWITHSUNDEEP
excelexcel-formulavba
Hewage
Hewage

Reputation: 185

Excel vba list issue

I have created a Command Button on my work sheet with the following codes. My excel file has more than 80 sheets. Now the issue in this list appears partially due to a big list. (first 40 items only)

How can I divide this list into 2 or 3 vertical lists?

Private Sub CommandButton1_Click()
Dim myList As String
    Dim mySht

    For i = 1 To ActiveWorkbook.Sheets.Count
        myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
    Next i

    mySht = InputBox("Select Sheet to go to." & vbCr & myList)

    If mySht = "" Then
        'MsgBox "User pressed CANCEL or empty string is submitted"
        Exit Sub
    End If

    If Not IsNumeric(mySht) Or mySht < 1 Or mySht > ActiveWorkbook.Sheets.Count Then
        MsgBox "Wrong input"
        Exit Sub
    End If
    ActiveWorkbook.Sheets(CInt(mySht)).Select

End Sub

Upvotes: 0

Views: 86

Answers (1)

Dave
Dave

Reputation: 1643

You are creating a list and entering it in the prompt where there isn't enough space. I suggest creating a custom form with a listbox like so:

Go to the VBA screen

Insert Userform

Add a Listbox

Add a Command button

Enter this code in the Userform module

Option Explicit

Private Sub UserForm_Initialize()
    Dim i As Long
    For i = 1 To ActiveWorkbook.Sheets.Count
        Me.ListBox1.AddItem ActiveWorkbook.Sheets(i).Name
    Next i
End Sub

Private Sub CommandButton1_Click()
    Dim myStr As String
    myStr = Me.ListBox1
    ActiveWorkbook.Sheets(myStr).Activate
    Me.Hide
End Sub

Then have your command button open the userform

Upvotes: 1

Related Questions