Solved

Excel:  Combobox to display selected choice after firing

Posted on 2011-02-16
2
634 Views
Last Modified: 2012-05-11
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
Comment
Question by:Maliki Hassani
2 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
ID: 34911705
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
 

Author Comment

by:Maliki Hassani
ID: 34911782
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now