Solved

Excel:  Combobox to display selected choice after firing

Posted on 2011-02-16
2
628 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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

772 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

11 Experts available now in Live!

Get 1:1 Help Now