Refreshing ListBox with new search results

M

mdshields

New Member
Joined
Jun 30, 2016
Messages
33
I've taken over a project from a co worker and am trying to take what he has already done and get a userform working for it. My problem is the listbox is not updating based on the new search results. The range that it references is updating on the spreadsheet but the listbox just won't show the updated values.

I'd also like to hide certain columns in the listbox based on the criteria laid out in the "Userform 4 Macros" area if possible. I understand I could set the column widths I want to hide to 0 but I'm not sure how I would do it based on the criteria given for the checkboxes.

Code: 
Private Sub cmdClear_Click()

Me.comboPressure.Text = ""
Me.comboPitch.Text = ""
Me.comboModule.Text = ""
Me.comboSplitPitch.Text = ""
Me.comboSplitModule.Text = ""
Me.comboKeyPart.Text = ""
Me.comboType.Text = ""
Me.comboPressure2.Text = ""
Me.comboGeneratingPitch.Text = ""
Me.comboGeneratingModule.Text = ""
Me.comboRampAngle.Text = ""
Me.comboRampLocation.Text = ""
Me.comboRampLocation2.Text = ""
Me.comboToothThickness.Text = ""
Me.comboToothThickness2.Text = ""
Me.comboAddendum.Text = ""
Me.comboAddendum2.Text = ""
Me.comboTipRadius.Text = ""
Me.comboTipRadius2.Text = ""
Me.comboFinishStock.Text = ""
Me.comboFinishStock2.Text = ""
Me.comboProtuberance.Text = ""
Me.comboProtuberance2.Text = ""
Me.comboCDimension.Text = ""
Me.comboCDimension2.Text = ""
Me.comboFlankAngle.Text = ""
Me.comboBDimension.Text = ""
Me.comboBDimension2.Text = ""
Me.comboWholeDepth.Text = ""
Me.comboWholeDepth2.Text = ""
Me.comboRootRadius.Text = ""
Me.comboRootRadius2.Text = ""
Me.comboThreads.Text = ""
Me.comboThreadHand.Text = ""
Me.comboGashes.Text = ""
Me.comboOutsideDiameter.Text = ""
Me.comboOutsideDiameter2.Text = ""
Me.comboOverallLength.Text = ""
Me.comboOverallLength2.Text = ""
Me.comboHole.Text = ""
Me.comboHole2.Text = ""


End Sub
'This is where the search command is
Private Sub CommandButton2_Click()

'''''''''''''''''''''''''''''''''''''''''Source for the listbox'''''''''''''''''''''''''''''''''''''''''''
Me.ListBox1.RowSource = Data

Dim ws As Worksheet
Set ws = Sheet1
With ws
.Range("C3").Value = Me.comboPressure.Value
.Range("D3").Value = Me.comboPitch.Value
.Range("E3").Value = Me.comboModule.Value
.Range("F3").Value = Me.comboSplitPitch.Value
.Range("G3").Value = Me.comboSplitModule.Value
.Range("H3").Value = Me.comboKeyPart.Value
.Range("I3").Value = Me.comboType.Value
.Range("J3").Value = Me.comboPressure2.Value
.Range("K3").Value = Me.comboGeneratingPitch.Value
.Range("L3").Value = Me.comboGeneratingModule.Value
.Range("M3").Value = Me.comboRampAngle.Value
.Range("N3").Value = Me.comboRampLocation.Value
.Range("O3").Value = Me.comboRampLocation2.Value
.Range("P3").Value = Me.comboToothThickness.Value
.Range("Q3").Value = Me.comboToothThickness2.Value
.Range("R3").Value = Me.comboAddendum.Value
.Range("S3").Value = Me.comboAddendum2.Value
.Range("T3").Value = Me.comboTipRadius.Value
.Range("U3").Value = Me.comboTipRadius2.Value
.Range("V3").Value = Me.comboFinishStock.Value
.Range("W3").Value = Me.comboFinishStock2.Value
.Range("X3").Value = Me.comboProtuberance.Value
.Range("Y3").Value = Me.comboProtuberance2.Value
.Range("Z3").Value = Me.comboCDimension.Value
.Range("AA3").Value = Me.comboCDimension.Value
.Range("AB3").Value = Me.comboFlankAngle.Value
.Range("AC3").Value = Me.comboBDimension.Value
.Range("AD3").Value = Me.comboBDimension.Value
.Range("AE3").Value = Me.comboWholeDepth.Value
.Range("AF3").Value = Me.comboWholeDepth2.Value
.Range("AG3").Value = Me.comboRootRadius.Value
.Range("AH3").Value = Me.comboRootRadius2.Value
.Range("AI3").Value = Me.comboThreads.Value
.Range("AJ3").Value = Me.comboThreadHand.Value
.Range("AK3").Value = Me.comboGashes.Value
.Range("AL3").Value = Me.comboOutsideDiameter.Value
.Range("AM3").Value = Me.comboOutsideDiameter2.Value
.Range("AN3").Value = Me.comboOverallLength.Value
.Range("AO3").Value = Me.comboOverallLength2.Value
.Range("AP3").Value = Me.comboHole.Value
.Range("AQ3").Value = Me.comboHole2.Value

End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'USERFORM 4 MACROS

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

    If CheckBox1.Value = True Then Sheets("Hob Cutters").Range("D:D,F:F,K:K,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AC:AC,AE:AE,AG:AG,AL:AL,AN:AN,AP:AP").EntireColumn.Hidden = False
        If CheckBox1.Value = True Then Sheets("CBN Form Wheels").Range("D:D,G:G,I:I,L:L,O:O,Q:Q,S:S,U:U,W:W,Y:Y").EntireColumn.Hidden = False
            'If CheckBox1.Value = True Then Sheets("Dressable Form Wheels").Range("D:D,G:G,I:I,L:L,O:O,Q:Q,S:S,U:U,W:W,Y:Y").EntireColumn.Hidden = False
    If CheckBox1.Value = False Then Sheets("Hob Cutters").Range("D:D,F:F,K:K,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AC:AC,AE:AE,AG:AG,AL:AL,AN:AN,AP:AP").EntireColumn.Hidden = True
        If CheckBox1.Value = False Then Sheets("CBN Form Wheels").Range("D:D,G:G,I:I,L:L,O:O,Q:Q,S:S,U:U,W:W,Y:Y").EntireColumn.Hidden = True
            'If CheckBox1.Value = False Then Sheets("Dressable Form Wheels").Range("D:D,G:G,I:I,L:L,O:O,Q:Q,S:S,U:U,W:W,Y:Y").EntireColumn.Hidden = True
        
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    If CheckBox2.Value = True Then Sheets("Hob Cutters").Range("E:E,G:G,L:L,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AD:AD,AF:AF,AH:AH,AM:AM,AO:AO,AQ:AQ").EntireColumn.Hidden = False
        If CheckBox2.Value = True Then Sheets("CBN Form Wheels").Range("E:E,H:H,J:J,M:M,P:P,R:R,T:T,V:V,X:X,Z:Z").EntireColumn.Hidden = False
            'If CheckBox2.Value = True Then Sheets("Dressable Form Wheels").Range("E:E,H:H,J:J,M:M,P:P,R:R,T:T,V:V,X:X,Z:Z").EntireColumn.Hidden = False
    If CheckBox2.Value = False Then Sheets("Hob Cutters").Range("E:E,G:G,L:L,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AD:AD,AF:AF,AH:AH,AM:AM,AO:AO,AQ:AQ").EntireColumn.Hidden = True
        If CheckBox2.Value = False Then Sheets("CBN Form Wheels").Range("E:E,H:H,J:J,M:M,P:P,R:R,T:T,V:V,X:X,Z:Z").EntireColumn.Hidden = True
            'If CheckBox2.Value = False Then Sheets("Dressable Form Wheels").Range("E:E,H:H,J:J,M:M,P:P,R:R,T:T,V:V,X:X,Z:Z").EntireColumn.Hidden = True
        
    
    Call SavSetting
    
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Me.ListBox1.RowSource = "Data"

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub SavSetting()
Dim ctl As Control
Dim CtrlType As String
For Each ctl In Me.Controls
    CtrlType = TypeName(ctl)
    If CtrlType = "TextBox" Or _
       CtrlType = "ComboBox" Or _
       CtrlType = "OptionButton" Or _
       CtrlType = "CheckBox" Then
        SaveSetting "EDFL", "Defaults", ctl.Name, CStr(ctl.Value)
    End If
Next ctl
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub CallSetting()
Dim ctl As Control
Dim CtrlType As String
For Each ctl In Me.Controls
    CtrlType = TypeName(ctl)
    If CtrlType = "TextBox" Or _
       CtrlType = "ComboBox" Or _
       CtrlType = "OptionButton" Or _
       CtrlType = "CheckBox" Then
        ctl.Value = VBA.GetSetting("EDFL", "Defaults", ctl.Name, CStr(ctl.Value))
    End If
Next ctl
End Sub



''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub CORRECTION()

Application.EnableEvents = True

End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub TEST()

Debug.Print

End Sub

'Reset Button
Private Sub CommandButton3_Click()
Me.CheckBox1.Value = False
Me.CheckBox2.Value = False
If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If

     
'Make All Data Visible
Sheets("Hob Cutters").Range("E:E,G:G,L:L,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AD:AD,AF:AF,AH:AH,AM:AM,AO:AO,AQ:AQ").EntireColumn.Hidden = False
Sheets("CBN Form Wheels").Range("E:E,H:H,J:J,M:M,P:P,R:R,T:T,V:V,X:X,Z:Z").EntireColumn.Hidden = False
Sheets("Hob Cutters").Range("D:D,F:F,K:K,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AC:AC,AE:AE,AG:AG,AL:AL,AN:AN,AP:AP").EntireColumn.Hidden = False
Sheets("CBN Form Wheels").Range("D:D,G:G,I:I,L:L,O:O,Q:Q,S:S,U:U,W:W,Y:Y").EntireColumn.Hidden = False

End Sub


Private Sub UserForm_Initialize()

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

    Call CallSetting

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

With Application
    Me.Top = .Top
    Me.Left = .Left
    Me.Height = .Height
    Me.Width = .Width
End With

'Make All Data Visible
Sheets("Hob Cutters").Range("E:E,G:G,L:L,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AD:AD,AF:AF,AH:AH,AM:AM,AO:AO,AQ:AQ").EntireColumn.Hidden = False
Sheets("CBN Form Wheels").Range("E:E,H:H,J:J,M:M,P:P,R:R,T:T,V:V,X:X,Z:Z").EntireColumn.Hidden = False
Sheets("Hob Cutters").Range("D:D,F:F,K:K,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AC:AC,AE:AE,AG:AG,AL:AL,AN:AN,AP:AP").EntireColumn.Hidden = False
Sheets("CBN Form Wheels").Range("D:D,G:G,I:I,L:L,O:O,Q:Q,S:S,U:U,W:W,Y:Y").EntireColumn.Hidden = False

End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You must log in or register to reply here.

Similar threads

G
  • Solved
Macro - Columns Are not Hidding / unhiding correctly
Replies
4
Views
146
GarethCW
G
P
  • Question
Help with Vba Code
Replies
3
Views
155
Coyotex3
C
S
  • Question
Excel macros extremely slow
Replies
4
Views
185
Shloime
S
J
  • Solved
Creating a VBA toggle button to show/hide multiple adjacent and non-adjacent columns in Excel
Replies
4
Views
746
dmt32
D
J
  • Question
VBA NumberFormat not working
Replies
5
Views
2K
RoryA
RoryA

Forum statistics

Threads
1,214,790
Messages
6,121,607
Members
449,037
Latest member
Arbind kumar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top