Userform to change the column width and row height

Question

I am creating a userform containing 2 textboxes, 4 different check boxes, 4 radial buttons and 2 command buttons, as seen below:

I want to change the row and column widths in the active sheet, or all the worksheets in a workbook, based on the selections in the form.

Frames

1. • TextBox1 (Column Width), TextBox2 (Row Height)
   • To type the row height and column width.
2. • Optionbutton1 (Column B onwards) , OptionButton2 (Column C onwards)
   • To select from which Column (B or C) you want to change the column width.
3. • Optionbutton3 (Selected Sheet), OptionButton4 (All sheets)
   • To select on which sheet you want to change the row height and column width ( On Active sheet or On All the sheets).
4. • CheckBox1 (Cover) , CheckBox2 (Trans_Letter), CheckBox3 (Abbreviations) CheckBox3 (Sheet ending with _Index)
   • One check box each for 4 of the sheets in my workbook. There are ~50 sheets in my workbook, these check boxes are for selecting which sheets to exclude while changing the column width and row height, when changing all of the sheets.

---

Please find below the code which I have put in the userform.

I am getting error on this line:

If IsError(WorksheetFunction.Match(ThisWorkbook.Worksheets(sheetNumber).Name, sheetsToExcludeArray, 0)) Then

Error Message: Run Time error '1004' Unable to get the Match property of the worksheet function

Private Sub CommandButton1_Click()    
    Dim startColumn As Long    
    Dim formatAllSheets As Boolean    
    Dim sheetsToExcludeList As String    
    Dim sheetNumber As Long    
    startColumn = 3
    If Me.OptionButton1.Value Then startColumn = 2    
    formatAllSheets = True    
    If Me.OptionButton3.Value Then formatAllSheets = False

    If Me.CheckBox1.Value Then sheetsToExcludeList = sheetsToExcludeList & ",Cover"
    If Me.CheckBox2.Value Then sheetsToExcludeList = sheetsToExcludeList & ",Trans_Letter"
    If Me.CheckBox3.Value Then sheetsToExcludeList = sheetsToExcludeList & ",Abbreviations"
    If Me.CheckBox4.Value Then sheetsToExcludeList = sheetsToExcludeList & ",Index"
    sheetsToExcludeList = Mid(sheetsToExcludeList, 2)

    Dim lastRow As Long    
    Dim lastColumn As Long    
    Dim itemInArray As Long    
    Dim rangeToFormat As Range    
    Dim sheetsToExcludeArray As Variant  

    If startColumn < 2 Or startColumn > 3 Then startColumn = 2
    sheetsToExcludeArray = Split(sheetsToExcludeList, ",")

    If formatAllSheets Then    
        For sheetNumber = 1 To ThisWorkbook.Worksheets.Count    
            If LBound(sheetsToExcludeArray) <= UBound(sheetsToExcludeArray) Then
                If IsError(WorksheetFunction.Match(ThisWorkbook.Worksheets(sheetNumber).Name, sheetsToExcludeArray, 0)) Then         
                    With ThisWorkbook.Worksheets(sheetNumber)                   
                        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row                
                        lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column                 
                        Set rangeToFormat = .Range(.Cells(1, startColumn), .Cells(lastRow, lastColumn))                 
                        rangeToFormat.Cells.RowHeight = me.textbox1.value
                        rangeToFormat.Cells.ColumnWidth = me.textbox2.value
                    End With    
                End If
            Else
                With ThisWorkbook.Worksheets(sheetNumber)               
                    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row                
                    lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column             
                    Set rangeToFormat = .Range(.Cells(1, startColumn), .Cells(lastRow, lastColumn))             
                    rangeToFormat.Cells.RowHeight = me.textbox1.value
                    rangeToFormat.Cells.ColumnWidth = me.texbox2.value
                End With    
            End If    
        Next sheetNumber    
    Else 
        With ThisWorkbook.Worksheets(sheetNumber)       
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row        
            lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column     
            Set rangeToFormat = .Range(.Cells(1, startColumn), .Cells(lastRow, lastColumn))     
            rangeToFormat.Cells.RowHeight = me.textbox1.value
            rangeToFormat.Cells.ColumnWidth = me.textbox2.value     
        End With    
    End If    
End Sub

Answer 1

Note, this answer uses an adaptation of the sub resizerowscols, which I wrote to answer your more recent question: Change column width and row height of hidden columns and rows (remaining hidden): Excel VBA

---

Main Click Sub

This (untested) sub takes the values from the form, then loops through the sheets (or just uses active sheet) and calls the other sub to do the resizing.

Sub CommandButton1_Click()
    ' Frame 1 values
    Dim colwidth As Double
    colwidth = Me.TextBox1.Value
    Dim rowheight As Double
    rowheight = Me.TextBox2.Value
    ' Frame 2 values
    Dim selectedCol As String
    If Me.OptionButton1.Value = True Then
        selectedCol = "B"
    Else
        selectedCol = "C"
    End If
    ' Frame 3 values
    Dim doAllSheets As Boolean
    doAllSheets = Me.OptionButton4.Value
    'Frame 4 values
    Dim sheetsToExcludeList As String
    If Me.CheckBox1.Value Then sheetsToExcludeList = sheetsToExcludeList & ",Cover"
    If Me.CheckBox2.Value Then sheetsToExcludeList = sheetsToExcludeList & ",Trans_Letter"
    If Me.CheckBox3.Value Then sheetsToExcludeList = sheetsToExcludeList & ",Abbreviations"
    If Me.CheckBox4.Value Then sheetsToExcludeList = sheetsToExcludeList & ",Index"
    ' Resizing
    Dim shtrng As Range
    Dim sht As Worksheet
    If doAllSheets Then
        ' Loop through sheets
        For Each sht In ThisWorkbook.Sheets
            ' Check sheet name isn't on exclude list
            If InStr(sheetsToExcludeList, "," & sht.Name) = 0 Then
                ' Set range equal to intersection of used range and columns "selected column" onwards
                Set shtrng = Intersect(sht.UsedRange, sht.Range(sht.Cells(1, selectedCol), sht.Cells(1, sht.Columns.Count)).EntireColumn)
                ' Resize columns / rows
                resizerowscols rng:=shtrng, w:=colwidth, h:=rowheight
            End If
        Next sht
    Else
        ' Just active sheet
        Set sht = ThisWorkbook.ActiveSheet
        Set shtrng = Intersect(sht.UsedRange, sht.Range(sht.Cells(1, selectedCol), sht.Cells(1, sht.Columns.Count)).EntireColumn)
        resizerowscols rng:=shtrng, w:=colwidth, h:=rowheight
    End If
End Sub

---

This is the adapted Sub from your other question, but now it takes the range, height and width as arguments. It unhides all rows/columns, resizes them, and re-hides all those which already were.

Sub resizerowscols(rng As Range, w As Double, h As Double)
' Resizes all rows and columns, including those which are hidden.
' At the end, hidden rows and columns remain hidden.
    If rng Is Nothing Then Exit Sub
    Dim n As Long
    Dim hiddencols() As Long
    Dim hiddenrows() As Long
    Application.ScreenUpdating = False
    ' Get hidden rows/cols
    ReDim hiddencols(rng.Columns.Count)
    ReDim hiddenrows(rng.Rows.Count)
    For n = 0 To UBound(hiddencols)
        hiddencols(n) = rng.Columns(n + 1).Hidden
    Next n
    For n = 0 To UBound(hiddenrows)
        hiddenrows(n) = rng.Rows(n + 1).Hidden
    Next n
    ' Unhide all
    rng.EntireColumn.Hidden = False
    rng.EntireRow.Hidden = False
    ' resize all
    rng.ColumnWidth = w
    rng.rowheight = h
    ' Re-hide rows/cols
    For n = 0 To UBound(hiddencols)
        rng.Columns(n + 1).Hidden = hiddencols(n)
    Next n
    For n = 0 To UBound(hiddenrows)
        rng.Rows(n + 1).Hidden = hiddenrows(n)
    Next n
    Application.ScreenUpdating = True
End Sub