vba userform , if any of the checkboxes in the frame is true then macro should not be applied on the sheetname mentioned in the checkbox

Question

I have created a userform (to change the column and row width of active sheet or all sheets )which has three frames. In the first frame I have given two option box. Firsts option box : - To change the row and column width from Column B onwards and other option box to change the row column width from column c onwards. User will select anyone of them and then move to second frame: which has again two options one to make the changes in active sheet and second option box to make the changes in all the sheets. So if the user in the first form will select first option (change row and column width from B onwards and in the second frame will select active sheet then the column and row width will change from Column B onwards in the active sheet and so on...

Now I want to create third fram which has 3 checkboxes which has name of 3 sheets (Sheet1, Sheet2 and Sheet3.) I want that when the user has selected his options in frame one and two if the user in the third fram select any of the checkboxes or all of the checkboxes then the changes should not apply in the sheetname mentioned in any of the 3 checkboxes which he has selected.

I have successfully executed frame one and frame 2 however struggling to create a code for frame 3 which will have 3 checkboxes (which contains name of 3 sheets) which is to excluded to make any row and column width changes.

Please find below my codes which are in the module:

Sub rowcolactivesheetb()
    Dim exworkb As Workbook
    Dim xlwksht As Worksheet
    Dim lastrow1 As Long
    Dim lastcolumn1 As Long
    Dim firstrowDB As Long
        With ActiveSheet
            lastrow1 = .Cells(Rows.Count, "A").End(xlUp).Row
            lastcolumn1 = .Cells(1, Columns.Count).End(xlToLeft).Column
            .Range(.Cells(1, 2), .Cells(lastrow1, lastcolumn1)).Select
            Selection.Cells.RowHeight = 9.14
            Selection.Cells.ColumnWidth = 7.14
        End With
End Sub



Sub rowcolallsheetb()
    Dim exworkb As Workbook
    Dim xlwksht As Worksheet
    Dim lastrow1 As Long
    Dim lastcolumn1 As Long
    Dim firstrowDB As Long
    Dim Z As Integer
    Dim ShtNames() As String

    ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count)
    For Z = 1 To Sheets.Count
        ShtNames(Z) = Sheets(Z).Name
        Sheets(Z).Select
        lastrow1 = Sheets(Z).Cells(Rows.Count, "A").End(xlUp).Row
        lastcolumn1 = Sheets(Z).Cells(1, Columns.Count).End(xlToLeft).Column
        ActiveWorkbook.Sheets(Z).Range(Sheets(Z).Cells(1, 2), Sheets(Z).Cells(lastrow1, lastcolumn1)).Select
        Selection.Cells.RowHeight = 9.14
        Selection.Cells.ColumnWidth = 7.14
    Next Z
End Sub

Sub rowcolactivesheetc()
    Dim exworkb As Workbook
    Dim xlwksht As Worksheet
    Dim lastrow1 As Long
    Dim lastcolumn1 As Long
    Dim firstrowDB As Long
    With ActiveSheet
        lastrow1 = .Cells(Rows.Count, "A").End(xlUp).Row
        lastcolumn1 = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Range(.Cells(1, 3), .Cells(lastrow1, lastcolumn1)).Select
        Selection.Cells.RowHeight = 9.14
        Selection.Cells.ColumnWidth = 7.14
    End With
End Sub

Sub rowcolallsheetc()

    Dim exworkb As Workbook
    Dim xlwksht As Worksheet
    Dim lastrow1 As Long
    Dim lastcolumn1 As Long
    Dim firstrowDB As Long
    Dim Z As Integer
    Dim ShtNames() As String

    ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count)

    For Z = 1 To Sheets.Count
        ShtNames(Z) = Sheets(Z).Name
        Sheets(Z).Select
        lastrow1 = Sheets(Z).Cells(Rows.Count, "A").End(xlUp).Row
        lastcolumn1 = Sheets(Z).Cells(1, Columns.Count).End(xlToLeft).Column
        ActiveWorkbook.Sheets(Z).Range(Sheets(Z).Cells(1, 3), Sheets(Z).Cells(lastrow1, lastcolumn1)).Select
        Selection.Cells.RowHeight = 9.14
        Selection.Cells.ColumnWidth = 7.14
    Next Z
End Sub

Userform code:

Private Sub CommandButton1_Click()
    If Me.OptionButton5.Value = True Then
        If Me.OptionButton7.Value = True Then
            Call rowcolactivesheetb
        ElseIf Me.OptionButton8.Value = True Then
            rowcolallsheetb
        End If
    End If
    If Me.OptionButton6.Value = True Then
        If Me.OptionButton7.Value = True Then
            Call rowcolactivesheetc
        ElseIf Me.OptionButton8.Value = True Then
            rowcolallsheetc
        End If
    End If
End Sub

Answer 1

First of all, I don't think I'd use OptionButtons. From your description it seems as if ListBoxes would suit you far better.

Secondly, it might be more elegant to pass the values into a single routine that actually sets the columns and rows rather than creating separate but almost identical routines.

I've stuck with your OptionButton structure and made the assumption that the three additional OptionButtons you allude to will be called OptionButton9, 10 & 11.

So the module code could be something like this:

Public Sub SizeRowsAndCols(fromB As Boolean, _
                           fromC As Boolean, _
                           targetActive As Boolean, _
                           targetAll As Boolean, _
                           excSheets As Variant)

    Dim fromCol As Long
    Dim sh As Worksheet
    Dim nameString As Variant

    'Define the column value
    Select Case True
        Case fromB: fromCol = 2
        Case fromC: fromCol = 3
        Case Else: MsgBox "Column selection error"
    End Select

    'Run routine on single or multiple sheets
    Select Case True
        Case targetActive
            SetValuesOnSheet ThisWorkbook.ActiveSheet, fromCol
        Case targetAll
            For Each sh In ThisWorkbook.Worksheets
                If IsEmpty(excSheets) Then
                    'If no sheets are to be excluded
                    SetValuesOnSheet sh, fromCol
                Else
                    'Exclude the sheets in the list
                    For Each nameString In excSheets
                        If sh.Name <> nameString Then
                            SetValuesOnSheet sh, fromCol
                        End If
                    Next
                End If
            Next
        Case Else
            MsgBox "Sheet selection error"
    End Select
End Sub

Private Sub SetValuesOnSheet(sh As Worksheet, fromCol As Long)
    Dim lastR As Long, lastC As Long
    Dim rng As Range

    With sh
        lastR = .Cells(.Rows.Count, "A").End(xlUp).Row
        lastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set rng = .Range(.Cells(1, fromCol), .Cells(lastR, lastC))
        rng.RowHeight = 9.14
        rng.ColumnWidth = 7.14
    End With
End Sub

And the UserForm code might be:

Private Sub CommandButton1_Click()
    Dim c As Long
    Dim sheetNames As String
    Dim list As Variant

    'Build the list of excluded sheets
    If OptionButton9.Value Then sheetNames = "Sheet1"
    If OptionButton10.Value Then sheetNames = IIf(sheetNames <> "", "|", "") & "Sheet2"
    If OptionButton11.Value Then sheetNames = IIf(sheetNames <> "", "|", "") & "Sheet3"
    list = IIf(sheetNames <> "", Split(sheetNames, "|"), Empty)

    'Call the generic routine
    SizeRowsAndCols OptionButton5.Value, _
                    OptionButton6.Value, _
                    OptionButton7.Value, _
                    OptionButton8.Value, _
                    list

End Sub