Question:
Is there a way to create a Userform like Microsoft Access has to hide
and unhide columns with data? Is there an example I can use?
Answer:
This is my final work code:
<CODE BEGIN>
Dim lbProc
Private Sub ListBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' Remembers ListBox Current Selection
ListBox1.ListIndex = -1
End Sub
Private Sub UserForm_Initialize()
'
Dim i As Long 'Integer
Dim ch As Variant
Dim rng As Range
lbProc = "False"
Application.ScreenUpdating = False
'1. Find last column with data
Set rng = Cells.Find(What:="*", After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious)
colnum = rng.Column + 1
'2. Check if columns hidden or visible
With ListBox1
.ColumnCount = 1
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
'3. Fill userform listbox all columns with data
For i = 1 To colnum - 1
collet = Left(Range(Columns(i), Columns(i)).Address(0, 0), 1)
.AddItem Range(collet & 1)
Next
'4. Select checkbox's with visible columns
iColNum = 1
For iCtr = 0 To .ListCount - 1
ch = Columns(iColNum).EntireColumn.Hidden
If Columns(iColNum).EntireColumn.Hidden = 0 Then .Selected
(iCtr) = True
iColNum = iColNum + 1
Next iCtr
End With
Application.ScreenUpdating = True
lbProc = "True"
End Sub
Private Sub ListBox1_Change()
'5. Click checkbox to hide or unhide column (checked=visible) &
updates listbox
If lbProc = "True" Then
If ListBox1.Selected(ListBox1.ListIndex) = True Then
Columns(ListBox1.ListIndex + 1).EntireColumn.Hidden =
False
Else 'If ListBox1.Selected(ListBox1.ListIndex) = False
Columns(ListBox1.ListIndex + 1).EntireColumn.Hidden = True
End If
End If
End Sub
<CODE END>
jfcby
