title: Excel VBA user form to display the sheet which is selected
body: <p>I have created a userform which has a listbox (ListBox1)which list down the sheet names and I can double click on the list and it will take me to that sheet. I have a back button (CommandButton2) when I click on back button it will take me to the previous selected sheet.
  I want to link my list box and back button so that when I click on back button the my listbox (ListBox1)should highlight the sheet where my back button (CommandButton2) has directed to.</p>
  
  <p>Please find below my codes:</p>
  
  <pre><code>Private Sub UserForm_Initialize()
  
  Dim Sh As Worksheet
  
  'for each loop the add visible sheets
  
  For Each Sh In ActiveWorkbook.Sheets
  
  'add sheets to the listbox
  
  Me.ListBox1.AddItem Sh.Name
  
  Next Sh
  
  End Sub
  
  Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  
  'declare the variables
  
  ' modifed code for ListBox double-click event, store the sheet name before switching to the selected item
  
  Dim i As Long
  
  LastSelectedSht = ActiveSheet.Name ' &lt;-- save the current active sheet before selecting a new one
  
  For i = 0 To ListBox1.ListCount - 1
  
  If ListBox1.Selected(i) Then
  
       Worksheets(ListBox1.List(i)).Activate
  
  End If
  
  Next i
  
  End Sub
  
  
  Private Sub CommandButton2_Click()
  
  Dim TmpSht As String
  
  TmpSht = ActiveSheet.Name ' &lt;-- save the current active sheet
  
  ' select the previous sheet (stored in LastSelectedSht)
  
  If LastSelectedSht = "" Then
  
      MsgBox "Error, no sheet stored , is it your first time running ? "
  
  Else
  
      Sheets(LastSelectedSht).Select
  
  End If
  
  LastSelectedSht = TmpSht ' &lt;-- use the temp variable to store the latest active sheet
  
  ' reset the userform
  
  Unload Me
  
  frmNavigation.Show
  
  End Sub
  </code></pre>