VBA - using listbox selections as criteria

[verycoolbreeze]

Hi,

Hope everyone is doing well. In advance i would like to thank you for viewing this post.

This is my first post so here goes:

I work for a company that use a massive spreadsheet to setup master data in our ERP. There are 4 functional areas involved in the process, with each functional area having tons of data to fill in. Needless to say this spreadsheet is not well liked and I am working on making it easier to use. What I would like to do is hide columns that are not relevant to any specific functional area. What I mean by this by example is if I work in the "Planning" dept, i don't care about the "WM" (warehouse management) columns and data and don't want to see them.

Each column in the spreadsheet has a header row assigning it to one of the 4 functional areas. This row has been defined/named as "header".

I created a form with a listbox and two buttons. One button is to cancel and close the list box, the other button's intent is to run a procedure whereby the selected items in the list box (multiple selections possible) get passed on as criteria and ultimately the relevant rows get hidden. When the procedure is hard coded as it is with "PLANNING" in my sample text below, it works fine, however I cannot figure out how to replace "PLANNING" with the listbox selected criteria. I have tried 3 to 4 different ways to no avail.

Thanks in advance for your time and input.

Thanks and best regards

Dennis
__________________________________________________________________
Private Sub UserForm_Initialize()
UserForm1.ListBox1.MultiSelect = 1
With ListBox1
.AddItem "PLANNING"
.AddItem "PROD.MANAGEMENT"
.AddItem "ACCOUNTING"
.AddItem "WM"
End With
End Sub

Private Sub CommandButton1_Click()
Dim Rng As Range
Set Rng = Range("header")
Application.ScreenUpdating = False
For Each Cell In Rng
'INSTEAD OF THE FIXED "PLANNING" VALUE BELOW, I WOULD LIKE TO BE ABLE TO SELECT ONE OR MORE SELECTIONS
'FROM THE LISTBOX1 OBJECT IN THE FORM. CAN'T FIGURE OUT HOW TO DO THIS...THANKS!
If Cell.Value = "PLANNING" Then
Cell.EntireColumn.Hidden = True
Else
Cell.EntireColumn.Hidden = False
End If
Next Cell
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click()
Sheets("EASTON FG TEMPLATE").Activate
ActiveSheet.Cells.EntireColumn.Hidden = False
Unload UserForm1
End Sub

 

[328mike]

Instead of making it complicated why don't you make sheet 1 the aggregated totals page with the finalized data, executive points, and graphical summary and have 4 different sheets for raw data input from each division?

 

[verycoolbreeze]

Thanks for your response. As an option, i might go down this road but it is not our first choice.

Thanks and best regards

Dennis

 

[328mike]

If you only have 4 columns of data to hide you could hard code them all in the form

If Cell.Value = "PLANNING" Then Cell.EntireColumn.Hidden = True
ElseIf Cell.Value = "Division2" Then Cell.EntireColumn.Hidden = True
ElseIf Cell.Value = "Division3" Then Cell.EntireColumn.Hidden = True
ElseIf Cell.Value = "Division4" Then Cell.EntireColumn.Hidden = True
Else Cell.EntireColumn.Hidden = False

If you have multiple columns for each division then you might be able to accomplish the same thing by defining a selection group. Do this by highlighting the relevant columns and defining them as 'Division1' 'Division2' ...etc.

 

[verycoolbreeze]

If you only have 4 columns of data to hide you could hard code them all in the form

If Cell.Value = "PLANNING" Then Cell.EntireColumn.Hidden = True
ElseIf Cell.Value = "Division2" Then Cell.EntireColumn.Hidden = True
ElseIf Cell.Value = "Division3" Then Cell.EntireColumn.Hidden = True
ElseIf Cell.Value = "Division4" Then Cell.EntireColumn.Hidden = True
Else Cell.EntireColumn.Hidden = False

If you have multiple columns for each division then you might be able to accomplish the same thing by defining a selection group. Do this by highlighting the relevant columns and defining them as 'Division1' 'Division2' ...etc.

Hi,

I ended up going the route pretty much as you suggested given there were only 4 variables. Also, instead of using a form, i just added a few command buttons in the worksheet and assigned them to the procedures.

Thanks for your help.

Here's what i ended up going with:
Sub planninghide()
Dim Rng As Range
Set Rng = Range("header")
Application.ScreenUpdating = False
For Each Cell In Rng
If Cell.EntireColumn.Hidden = True Then
'do nothing
Else
If Cell.Value = "PLANNING" Then
Cell.EntireColumn.Hidden = True
Else
Cell.EntireColumn.Hidden = False
End If
End If
Next Cell
Application.ScreenUpdating = True
End Sub

Sub marketinghide()
Dim Rng As Range
Set Rng = Range("header")
Application.ScreenUpdating = False
For Each Cell In Rng
If Cell.EntireColumn.Hidden = True Then
'do nothing
Else
If Cell.Value = "PROD. MANAGEMENT" Then
Cell.EntireColumn.Hidden = True
Else
Cell.EntireColumn.Hidden = False
End If
End If
Next Cell
Application.ScreenUpdating = True
End Sub

Sub wmhide()
Dim Rng As Range
Set Rng = Range("header")
Application.ScreenUpdating = False
For Each Cell In Rng
If Cell.EntireColumn.Hidden = True Then
'do nothing
Else
If Cell.Value = "WM" Then
Cell.EntireColumn.Hidden = True
Else
Cell.EntireColumn.Hidden = False
End If
End If
Next Cell
Application.ScreenUpdating = True
End Sub
Sub accountinghide()
Dim Rng As Range
Set Rng = Range("header")
Application.ScreenUpdating = False
For Each Cell In Rng
If Cell.EntireColumn.Hidden = True Then
'do nothing
Else
If Cell.Value = "ACCOUNTING" Then
Cell.EntireColumn.Hidden = True
Else
Cell.EntireColumn.Hidden = False
End If
End If
Next Cell
Application.ScreenUpdating = True
End Sub
Sub nahide()
Dim Rng As Range
Set Rng = Range("header")
Application.ScreenUpdating = False
For Each Cell In Rng
If Cell.EntireColumn.Hidden = True Then
'do nothing
Else
If Cell.Value = "N/A" Then
Cell.EntireColumn.Hidden = True
Else
Cell.EntireColumn.Hidden = False
End If
End If
Next Cell
Application.ScreenUpdating = True
End Sub
Sub allshow()
Sheets("EASTON FG TEMPLATE").Activate
ActiveSheet.Cells.EntireColumn.Hidden = False
End Sub