VBA Dynamic Hide/Unhide multiple columns on subform 1

[Reggaefloww]

Hi all

I have used Microsoft Access for a few years and I would like to use VBA to hide/unhide several columns on a subform. I'm sure there is a simple answer to it, but I can't seem to find anyone who has spelled it out yet. I would like to use a multiselect list box to hide/unhide fields in a subform. I've looked for this all over the internet, and only Allen Browne seemed to know what it was. The only problem was he didn't write out how to do it. Thanks guys.

Reggaefloww

 

[Reggaefloww]

Opps, this is my first post to this forum. I created the post as a programmer instead of as a technical user.

 

[MajP]

My subform is named mySubform

My list 2 holds the names of my controls
As I click through my multi select it shows or hides fields in a data sheet view.

Private Sub List2_AfterUpdate()
  Dim intCounter As Integer
  Dim strControl As String
  For intCounter = 0 To List2.ListCount - 1
    If List2.Selected(intCounter) Then
      blnHide = True
    End If
    strControl = List2.ItemData(intCounter)
    Me.mySubForm.Form.Controls(strControl).ColumnHidden = blnHide
    blnHide = False
  Next intCounter
End Sub

 

[MajP]

By the way, that is a pretty neat idea. I think I will use it in the future.

 

[MajP]

One more thing. On the on open event you probably want to write code to unhide all columns. You can end up hiding all or most columns and when you open the form the user may not know to click in the list box to unhide the selections.

 

[Reggaefloww]

Thanks MajP for your quick response, but what's blnHide?

 

[Reggaefloww]

LOL....never mind. Very helpful. Thanks

 

[MajP]

Actually I could have done it a lot more compact, but I thought it might be hard to follow.

Private Sub List2_AfterUpdate()
  Dim intCounter As Integer
  For intCounter = 0 To List2.ListCount - 1
    me.mySubForm.Form.Controls(List2.ItemData(intCounter)).ColumnHidden = List2.Selected(intCounter)
  
  Next intCounter
End Sub

 

[Reggaefloww]

Thanks MajP

Each solution solved the problem. It was a great help. How would you hide all the subform columns when you opened the main form? As you'll notice, I also gave you a star.

 

[MajP]

One way would be to use the same code in the form's on open event but set everything to false.

Private Sub form_open()
Dim intCounter As Integer
For intCounter = 0 To List2.ListCount - 1
me.mySubForm.Form.Controls(List2.ItemData(intCounter)).ColumnHidden = False
Next intCounter
End Sub

 

[Reggaefloww]

That didn't seem to work for me.

 

[Reggaefloww]

I guess I should start a new post lol, since you did solve the original problem.

 

[MajP]

Oops. I said columnhidden = false, I should have said True if you want to hide not show.

 

[Reggaefloww]

I already tried that lol.