Solved

Excel:  Combobox to display selected choice after firing

Posted on 2011-02-16
2
642 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

726 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