Consulting

Results 1 to 3 of 3

Thread: Revising a code that inserts a new option column upon execution of an option button

  1. #1

    Revising a code that inserts a new option column upon execution of an option button

    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
    Last edited by LizCorbert; 02-03-2016 at 09:23 AM. Reason: Applied code tags

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Thanks so much for the code and explanation! I really appreciate it. Thank you for explaining first and last column coding to me.

    Liz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •