UserForm ListBox

[jfcby]

Hello,

I'm tring to create a userform that will list all columns in a
worksheet and if they are hidden or unhidden. Then when I click a
column in the listbox it will check the status and either hide or
uhide.

1. Problem One: is that when I make a selection the listbox is cleared
so that the status can be updated but the selection is not remembered.
How, after I make a selection and the listbox is updated it will be
remembered?

2. Problem 2: is that after I make a selection and the listbox is
updated if I reselect the same column it does nothing? How can the
code be modified so that after I make a selection and the listbox is
updated I will be able to reselect the same column again to either
hide or unhide the column?

<CODE BEGIN>

Private Sub UserForm_Initialize()
'
Call ListBox1_Main

End Sub

Private Sub ListBox1_Main()
Dim i As Integer
Dim ch As Variant
Dim rng As Range

'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 = 2
.ColumnWidths = "100,75" '.Width - 1 & ";0"
For i = 1 To colnum
collet = Left(Range(Columns(i), Columns(i)).Address(0, 0),
1)
'rng = Range(collet & "1")
'MsgBox rng
ch = Columns(i).EntireColumn.Hidden
If ch = 0 Then
ch = "Visible"
Else 'If ch = -1 Then
ch = "Hidden"
End If
'3. Fill userform listbox all columns with data & if hidden or
visible
.AddItem
.AddItem
.List(i, 0) = Range(collet & 1) '.List(i, 0) = Range
(collet & 1)
.List(i, 1) = ch

Next

End With
'4. In userform select column then click button hide or unhide
'5. Update userform

End Sub

Private Sub ListBox4_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
ListBox1.List = -1
End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
ListBox1.ListIndex = -1
End Sub

Private Sub ListBox1_Click()
'Hide or Unhide Column
With Me.ListBox1
If Columns(.ListIndex).EntireColumn.Hidden = True Then
Columns(.ListIndex).EntireColumn.Hidden = False
Else
Columns(.ListIndex).EntireColumn.Hidden = True
End If
.Clear
End With
Call ListBox1_Main
End Sub

<CODE END>

Thank you for your help,
jfcby

 

[Steve Yandl]

With any luck, this will post without breaking any of the lines.

To abbreviate a bit, I set up ListBox1 on UserForm1 but used the property
window for ListBox1 to set it for 2 columns that were 30pt and 70pt wide.
In my tests, this seemed to do what I think you want.

'---------------------------------------------

Private C As Long

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, _
ByVal X As Single, ByVal Y As Single)

C = ActiveSheet.UsedRange.Columns.Count

If ActiveSheet.Columns(ListBox1.ListIndex + 1).EntireColumn.Hidden = True
Then
ActiveSheet.Columns(ListBox1.ListIndex + 1).EntireColumn.Hidden = False
Else
ActiveSheet.Columns(ListBox1.ListIndex + 1).EntireColumn.Hidden = True
End If

With ListBox1
For X = 1 To C
If ActiveSheet.Columns(X).EntireColumn.Hidden Then
.Column(1, X - 1) = "hidden"
Else
.Column(1, X - 1) = "visible"
End If
Next X
End With
End Sub

Private Sub UserForm_Activate()

C = ActiveSheet.UsedRange.Columns.Count

With ListBox1
For X = 1 To C
.AddItem
.Column(0, X - 1) = ActiveSheet.Columns(X).Address(0, 0)
If ActiveSheet.Columns(X).EntireColumn.Hidden Then
.Column(1, X - 1) = "hidden"
Else
.Column(1, X - 1) = "visible"
End If
Next X
End With

End Sub

'---------------------------------------------

Steve Yandl

 

[jfcby]

Steve thank you for your help.

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