PDA

View Full Version : [SOLVED] Excel 2013 ActiveX Combo Boxes Macro Glitch



aworthey
05-04-2016, 07:23 AM
Hello,

Has anyone experienced any glitches with activex combo boxes that use macros to populate the menus?

I found code online and adapted it to populate a drop down menu and its dependent drop down menu. It works perfectly. However, when I first open the workbook, I have to click on another sheet within the workbook then click on the dashboard sheet (which is the home for these menus) for the drop down menus to populate. Otherwise, everything works perfectly.

I appreciate any suggestions!

Here's the code I'm using:


Option Explicit


Sub cboCategoryList_Change()


Application.ScreenUpdating = False


Dim rng As Range
Dim Ws As Worksheet
Dim str As String
Set Ws = Worksheets("CompanyLookup")
str = cboCategoryList.Value
Me.cboDependentList.Clear
On Error Resume Next
For Each rng In Ws.Range(str)
Me.cboDependentList.AddItem rng.Value
Next rng

Application.ScreenUpdating = True


End Sub
Sub Worksheet_Activate()


Application.ScreenUpdating = False


Dim rng As Range
Dim Ws As Worksheet
Set Ws = Worksheets("CompanyLookup")
Me.cboCategoryList.Clear
For Each rng In Ws.Range("Category")
Me.cboCategoryList.AddItem rng.Value
Next rng

Application.ScreenUpdating = True


End Sub


Sub cboDependentList_DropButtonClick()


Application.ScreenUpdating = False


Dim CAT As String
CAT = Worksheets("ResponseRateTables").Range("B2").Value
Dim DEP As String
DEP = Worksheets("ResponseRateTables").Range("B1").Value






If CAT = "Individual" Then


With Worksheets("Dashboard")
.ChartObjects("QuotesInfo").Visible = True: .ChartObjects("3rdParty").Visible = True: .ChartObjects("GroupChart").Visible = False: .ChartObjects("GroupChart3P").Visible = False
End With

Worksheets("Dashboard").ChartObjects("QuotesInfo").Select
With ActiveChart.Axes(xlValue, xlPrimary)
.MaximumScale = Sheet8.Range("B45").Value: .MinimumScale = Sheet8.Range("B46").Value: .MajorUnit = Sheet8.Range("B47").Value
End With

Worksheets("Dashboard").ChartObjects("3rdParty").Select
With ActiveChart.Axes(xlValue, xlPrimary)
.MaximumScale = Sheet8.Range("E45").Value: .MinimumScale = Sheet8.Range("E46").Value: .MajorUnit = Sheet8.Range("E47").Value
End With



ElseIf CAT = "Group" And DEP = "% of Total Requests" Then


With Worksheets("Dashboard")
.ChartObjects("QuotesInfo").Visible = False: .ChartObjects("3rdParty").Visible = False: .ChartObjects("GroupChart").Visible = True: .ChartObjects("GroupChart3P").Visible = True:
End With

Worksheets("Dashboard").ChartObjects("GroupChart").Select
With ActiveChart.Axes(xlValue, xlPrimary)
.TickLabels.NumberFormat = "0%": .MaximumScale = 1.1: .MajorUnit = 0.2: .AxisTitle.Text = "% of Total Requests"
End With

Worksheets("Dashboard").ChartObjects("GroupChart3P").Select
With ActiveChart.Axes(xlValue, xlPrimary)
.TickLabels.NumberFormat = "0%": .MaximumScale = 1.1: .MajorUnit = 0.2: .AxisTitle.Text = "% of Total Requests"
End With




ElseIf CAT = "Group" And DEP = "On-Time %" Then


With Worksheets("Dashboard")
.ChartObjects("QuotesInfo").Visible = False: .ChartObjects("3rdParty").Visible = False: .ChartObjects("GroupChart").Visible = True: .ChartObjects("GroupChart3P").Visible = True
End With


Worksheets("Dashboard").ChartObjects("GroupChart").Select
With ActiveChart.Axes(xlValue, xlPrimary)
.TickLabels.NumberFormat = "0%": .MaximumScale = 1.1: .MajorUnit = 0.2: .AxisTitle.Text = "On-Time %"
End With

Worksheets("Dashboard").ChartObjects("GroupChart3P").Select
With ActiveChart.Axes(xlValue, xlPrimary)
.TickLabels.NumberFormat = "0%": .MaximumScale = 1.1: .MajorUnit = 0.2: .AxisTitle.Text = "On-Time %"
End With


ElseIf CAT = "Group" And DEP = "# of Requests" Then


With Worksheets("Dashboard")
.ChartObjects("QuotesInfo").Visible = False: .ChartObjects("3rdParty").Visible = False: .ChartObjects("GroupChart").Visible = True: .ChartObjects("GroupChart3P").Visible = True
End With




Worksheets("Dashboard").ChartObjects("GroupChart").Select
With ActiveChart.Axes(xlValue, xlPrimary)
.TickLabels.NumberFormat = "0": .MaximumScale = Sheet16.Range("X29").Value: .MinimumScale = Sheet16.Range("X30").Value: .MajorUnit = Sheet16.Range("X31").Value: .AxisTitle.Text = "# of Requests"
End With

Worksheets("Dashboard").ChartObjects("GroupChart3P").Select
With ActiveChart.Axes(xlValue, xlPrimary)
.TickLabels.NumberFormat = "0": .MaximumScale = Sheet16.Range("AA29").Value: .MinimumScale = Sheet16.Range("AA30").Value: .MajorUnit = Sheet16.Range("AA31").Value: .AxisTitle.Text = "# of Requests"
End With



End If



ActiveCell.Offset(0, 0).Activate

Application.ScreenUpdating = True




End Sub

Aflatoon
05-04-2016, 09:13 AM
That's not really a glitch - you use the Worksheet_Activate event to populate them and, in order to trigger that, you have to switch to that sheet. You could put the code into a separate routine and call that from the Activate event and from Workbook_Open.

aworthey
05-04-2016, 09:32 AM
That's not really a glitch - you use the Worksheet_Activate event to populate them and, in order to trigger that, you have to switch to that sheet. You could put the code into a separate routine and call that from the Activate event and from Workbook_Open.

Thank you for responding! Is this what you're suggesting?


Sub Workbook_Open()


Application.ScreenUpdating = False


Dim rng As Range
Dim Ws As Worksheet
Set Ws = ThisWorkbook.Worksheets("CompanyLookup")
Me.cboCategoryList.Clear
For Each rng In Ws.Range("Category")
Me.cboCategoryList.AddItem rng.Value
Next rng

Application.ScreenUpdating = True


End Sub

aworthey
05-04-2016, 10:26 AM
That's not really a glitch - you use the Worksheet_Activate event to populate them and, in order to trigger that, you have to switch to that sheet. You could put the code into a separate routine and call that from the Activate event and from Workbook_Open.

Ah, I understand now...Thanks so much!! This is what I did...

ThisWorkbook:


Option Explicit


Sub workbook_open()
Sheet5.FillcboCategoryList


End Sub


In the Sheet where the boxes reside:


Sub FillcboCategoryList()


Application.ScreenUpdating = False


Dim rng As Range
Dim Ws As Worksheet
Set Ws = ThisWorkbook.Worksheets("CompanyLookup")
Me.cboCategoryList.Clear
For Each rng In Ws.Range("Category")
Me.cboCategoryList.AddItem rng.Value
Next rng

Application.ScreenUpdating = True


End Sub