Cannot close workbook after running macro with userform and listbox

Question

I am using the code below to make navigation through workbook easier. However, after running the macro cursor movement is odd (restricted range and jumping). In addition the workbook does not close when clicking on the red "x" to close the window. Further, the group and ungroup icons (for hiding or expanding rows and columns) does not work. It appears that somehow "control" is still in VBA as supposed to normal Excel. All help seriously appreciated.

Userform code:

Option Explicit
Option Base 1

Private Sub UserForm_Initialize()
    Dim ShList()
    Dim ShCount As Integer
    Dim x As Integer
    Dim ListPos As Integer

    ShCount = ActiveWorkbook.Sheets.Count
    ReDim Preserve ShList(1 To ShCount)

    For x = 1 To ShCount
        If Sheets(x).Name = ActiveSheet.Name Then
            ListPos = x - 1
        End If
        ShList(x) = Sheets(x).Name
    Next x

    With ListBox1
        .List = ShList
        .ListIndex = ListPos
    End With

End Sub

Private Sub CommandButton1_Click()
    Sheets(ListBox1.Value).Activate
    Unload UserForm1
End Sub

Click button to activate code:

Option Explicit

Sub WorksheetSelect_Click()
    UserForm1.Show
End Sub

Answer 1

I had the same issue and had to remove the following code: Sheets(1).Activate

After removing it, I was able to close the Excel as always.

Answer 2

I had the same problem. It was solved by changing any cell value in any cell on the active worksheet as last row in my VBA code. In my case I added the VBA code: Range("A1") = "_", but you could also change any other cell to any other value.

Answer 3

sorry not using comment, because its too long, just analyzing your code

Option Explicit
Option Base 1 'someone tell me what's this ? i might be noob at this point

Private Sub UserForm_Initialize()
Dim ShList() 'as variant
Dim ShCount As Integer 'as Long
Dim x As Integer 'as long
Dim ListPos As Integer 'as Long

ShCount = ActiveWorkbook.Sheets.Count 
ReDim Preserve ShList(1 To ShCount) 'remove preserve

For x = 1 To ShCount
    If Sheets(x).Name = ActiveSheet.Name Then
        ListPos = x - 1
    End If
    ShList(x) = Sheets(x).Name
Next x

With ListBox1 'i usually write : with Me.Listbox1, but i guess its ok
    .List = ShList
    .ListIndex = ListPos
End With

'add erase ShList (free memory)
End Sub

so main thing, remove Preserve, and add a Erase for your Array

Answer 4

I have a problem that turn my head for more than three days now, the problem is that I am using a data-entry app using vba , I have linked some other excel workbooks to my database and when I open those linked files from the list box: they open normally but they do not wanna close unless I close my app , if any one can help me I would be gratfule. here is my vba:

Private Sub cmbfichedevie_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")
Set shsearch = ThisWorkbook.Sheets("Searchdata")
Dim iRow As Long
Dim iRowsearchdat As Long
Dim ttt As Long

With shsearch
ttt = Selected_list + 1
iRowsearchdata = [Counta(Searchdata!A:A)]
If iRowsearchdata > 1 Then
    If Selected_list >= iRowsearchdata Or Selected_list = 0 Then
    MsgBox "Aucun élément n'est sélectionné.", vbOKOnly + vbInformation, "Ouvrir"
    Else
        If shsearch.Cells(ttt, 12) = "" Or shsearch.Cells(ttt, 12) = "No file" Then
        MsgBox "Il n'y a aucune fiche de vie pour cet article.", vbOKOnly + vbInformation, "Ouvrir"
        Else
        'shsearch.Cells(ttt, 12).Hyperlinks(1).Follow
        Workbooks.Open .Cells(ttt, 12)
        'Unload Me
        End If
    End If
    Exit Sub
Else
With sh
ttt = Selected_list + 1
iRow = [Counta(Database!A:A)]

If Selected_list >= iRow Or Selected_list = 0 Then
MsgBox "Aucun élément n'est sélectionné.", vbOKOnly + vbInformation, "Ouvrir"
Else

    If sh.Cells(ttt, 12) = "" Or sh.Cells(ttt, 12) = "No file" Then
    MsgBox "Il n'y a aucune fiche de vie pour cet article.", vbOKOnly + vbInformation, "Ouvrir"
    Else
    'sh.Cells(ttt, 12).Hyperlinks(1).Follow NewWindow:=True
    Workbooks.Open .Cells(ttt, 12)
    'Unload Me
    End If
End If
End With
End If
End With
End Sub