PDA

View Full Version : Revising a code that inserts a new option column upon execution of an option button



LizCorbert
02-03-2016, 08:21 AM
I have the following code that unhides a column each time the button is selected. The unhiding of columns works fine, however, the order of the columns are reversed. For instance, the columns being unhidden are named "Option 1", "Option 2", "Option 3".................. through "Option 25". My problem is the unhidden columns are brought in before each column instead of after. For instance instead of Option 1, Option 2, Option 3. My code produces Option 3, Option 2, Option 1. Can someone assist me in revising this code, please!


Worksheets("Property").Activate
Dim col AsInteger, CNo AsInteger
For col =1To Columns.Count
If Columns(col).Hidden =TrueThen
CNo = col
EndIf
Next col
If CNo >0Then
Columns(CNo).Hidden =False
EndIf

SamT
02-03-2016, 10:33 AM
Cno will always be the last hidden column

For col = 1 To Columns.Count

Cno will always be the first hidden column

For col = Columns.Count to 1 Step -1


Dim Cno as Long 'Module level Variable

Sub Sequential()
'Shows columns in order from 1 to LastCol, If LastCol is Visible, shows Col 1 again.

Dim Col As Long
Dim LastCol
LastCol = Cells(1, Columns.Count).End(xlToleft).Column

'On first run when Workbook is Opened, Initialize Cno
If Cno = 0 Then
For Col =1 To LastCol
If Not Columns(Col).Hidden Then
Cno = Col
Exit For
End If
Next
End If

Columns(Cno). Hidden = True

'Increment Cno
If Cno < LastCol then
Cno = Cno + 1
Else: Cno = 1
End If

Columns(Cno).Hidden = False
End Sub

LizCorbert
02-03-2016, 11:14 AM
Thanks so much for the code and explanation! I really appreciate it. Thank you for explaining first and last column coding to me.

Liz