Link to home
Start Free TrialLog in
Avatar of Maliki Hassani
Maliki HassaniFlag for United States of America

asked on

Excel: Combobox to display selected choice after firing

Experts,

Question I have a combo box that has 4 choices to select (reports).  When selected it will open up the specific sheet.  That script is working fine.  However, when it changes the sheet, the combobox displays the previous choice and not the selected report.  

Example:  I select "Site Alerts Posted within 15 Minutes" and after the sheet changes, the combobox displays "Change Control: Non - Compliance".

How can I get this to display the currect selection on the new sheet?

Thanks NOC-Reports-r19.xlsm
Private Sub ComboBox1_Change()
Dim Target As String, myRng As Variant
Dim WS As Worksheet


    Target = ComboBox1.Value
    
    If Target = "BHN Tickets Escalated to fix agent within 15 Minutes" Then
    Call TurnEventsBackOn
    Application.ScreenUpdating = False
    Sheets("BHN Dashboard").Visible = True
      For Each sh In ThisWorkbook.Worksheets
       If Not sh.Name = ("BHN Dashboard") Then sh.Visible = False
   Next sh
      
        Else
   If Target = "DAC Tickets Escalated to fix agent within 15 Minutes" Then
   Call TurnEventsBackOn
    Application.ScreenUpdating = False
    Sheets("DAC Dashboard").Visible = True
      For Each sh In ThisWorkbook.Worksheets
       If Not sh.Name = ("DAC Dashboard") Then sh.Visible = False
   Next sh
     
        Else
   If Target = "Site Alerts Posted within 15 Minutes" Then
   Call TurnEventsBackOn
     Application.ScreenUpdating = False
    Sheets("Site Alerts Dashboard").Visible = True
      For Each sh In ThisWorkbook.Worksheets
       If Not sh.Name = ("Site Alerts Dashboard") Then sh.Visible = False
   Next sh
  
   
        Else
   If Target = "Change Control: Non - Compliance" Then
   Call TurnEventsBackOn
    Application.ScreenUpdating = False
    Sheets("MNT Dashboard").Visible = True
      For Each sh In ThisWorkbook.Worksheets
       If Not sh.Name = ("MNT Dashboard") Then sh.Visible = False
   Next sh
    
   
End If
End If
End If
End If

   
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of telyni19
telyni19
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Maliki Hassani

ASKER

telyni19:  

Thank you!  This will work so much better.  I guess I am not used to doing it this way, but it is the best way..  :)