Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Useform displaying all columns with data.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
useform label enter | Excel Discussion (Misc queries) | |||
Selecting data from columns and displaying them as rows | Excel Discussion (Misc queries) | |||
Excel: useform and shapes | Excel Programming | |||
Listbox on a useform | Excel Discussion (Misc queries) | |||
Useform ComboBox to Worksheet | Excel Programming |