• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

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

0
Maliki Hassani
Asked:
Maliki Hassani
1 Solution
 
telyni19Commented:
It displays a different choice because you have a separate combo box for each sheet. If you were to go back to the previous sheet, you would see that the choice you just made is still chosen there, but not on the new sheet.

You also declare a WS as a worksheet, but don't use it for anything. I suggest assigning the chosen sheet to that variable, and then using it later to set the value of the combobox on that sheet to the chosen value.

What you really want to do though is have all four combo boxes call a separate procedure; that way you wouldn't have to have four copies of the code and have to make any changes four times. I also suggest using a Select Case structure rather than nested If's. See the attached code and updated file for what I've done.
Sub ChooseReportSheet()
Dim Target As String
Dim intIndex As Integer
Dim WS As Worksheet

    Target = ActiveSheet.ComboBox1.Value
    intIndex = ActiveSheet.ComboBox1.ListIndex
    
    Select Case Target
    
    Case "BHN Tickets Escalated to fix agent within 15 Minutes"
        Set WS = Sheets("BHN Dashboard")
    
    Case "DAC Tickets Escalated to fix agent within 15 Minutes"
        Set WS = Sheets("DAC Dashboard")
    
    Case "Site Alerts Posted within 15 Minutes"
        Set WS = Sheets("Site Alerts Dashboard")

    Case "Change Control: Non - Compliance"
        Set WS = Sheets("MNT Dashboard")
    
    End Select

    Call TurnEventsBackOn
    Application.ScreenUpdating = False
    WS.Visible = True
    For Each sh In ThisWorkbook.Worksheets
       If Not sh.Name = WS.Name Then sh.Visible = False
    Next sh
    WS.Activate
    ActiveSheet.ComboBox1.ListIndex = intIndex
End Sub

Open in new window

NOC-Reports-r19.xlsm
0
 
Maliki HassaniAuthor Commented:
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..  :)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now