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
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