Run different macros based on userform checkbox values

Question

Have been looking around to see if I could find the solution to my problem myself, but I have come up short..

Basically I am creating a userform containing 4 different check boxes, [4] optionbuttons & 1 commandbutton.

First Frame - Optionbutton5 (Column B onwards) , OptionButton6 (Column C onwards)

Second Frame - Optionbutton7 (Selected Sheet), OptionButton8 (All sheets)

Third Frame - CheckBox1 (Cover) , CheckBox2 (Trans_Letter), CheckBox3 (Abbreviations) CheckBox3 (Indexes)

This user form helps me to change the row and column width of activesheet OR all the worksheets in a workbook This userform has 3 frames:

1st frame: To select from which Column (B or C) you want to change the column width.

2nd frame : To select on which sheet you want to change the row height and column width ( On Active sheet or On All the sheets)

3rd frame : It has 4 checkboxes which contains name of 4 sheets in my workbook. Although there are close to 50 sheets in my workbook but I have created checkboxes for these specific 4 sheets because whenever needed I can select any of the checkbox and that sheet is excluded while changing the column width and row height of all the sheets.

I have developed macros to change the column width and row height from Column (B or C) and from activesheet and all the sheets and these macros work absolutely fine. Till now I am successful in linking my 1st and second frame (Eg : when I am selecting "Column B onwards" in first frame and "All Sheets " in second frame it is changing the column width and row height. Now I want to link my third frame that once I select "Column B onwards" in first frame and "All Sheets " in second frame and "Cover" in the third frame then it should change column width and row height of all the sheets except the Sheet name "Cover".

Can you help me with the code that whenever any of the checkboxes are TRUE then for that respective sheet the macro should not apply that is column and row height and width dosent change.

Module Codes:

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.4
    Selection.Cells.ColumnWidth = 11.2
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 ActiveWorkbook.Sheets.Count
    ShtNames(Z) = Sheets(Z).Name
    Sheets(Z).Activate
    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.4
    Selection.Cells.ColumnWidth = 11.2
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.4
    Selection.Cells.ColumnWidth = 11.2
End With

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.4
    Selection.Cells.ColumnWidth = 11.2
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.4
    Selection.Cells.ColumnWidth = 11.2
Next Z

End Sub

Sub rowcolallsheetbcover()

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
    If Sheets(Z).Name <> "Cover" Then
        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
    End If
Next Z

End Sub

Private Sub CommandButton1_Click()

If Me.OptionButton5.Value = True Then
    If Me.OptionButton7.Value = True Then
        Call rowcolactivesheetb
    End If
End If

If Me.OptionButton6.Value = True Then
    If Me.OptionButton7.Value = True Then
        Call rowcolactivesheetc
    End If
End If

If Me.OptionButton5.Value = True Then
    If Me.OptionButton8.Value = True Then
        If Me.CheckBox1.Value = True Then
            Call rowcolallsheetbcover
        Else
            Call rowcolallsheetb
        End If
    End If
End If

If Me.OptionButton6.Value = True And _
    Me.OptionButton8.Value = True And _
    Me.CheckBox1.Value = False And _
    Me.CheckBox2.Value = False And _
    Me.CheckBox3.Value = False And _
    Me.CheckBox4.Value = False Then
        Call rowcolallsheetc
End If

If Me.OptionButton6.Value = True And _
    Me.OptionButton8.Value = True And _
    Me.CheckBox1.Value = True Then
        Call rowcolallsheetccover
End If

If Me.OptionButton6.Value = True And _
    Me.OptionButton8.Value = True And _
    Me.CheckBox2.Value = True Then
        Call rowcolallsheetctransletter
End If

End Sub

It seems that we are missing a lot of code in order to help you. There are a lot of subs being calling in the above code snippet and we do not know what this code does. Also, you mentioned in your post that you'd attach the userform (to this post?). Yet, I cannot see the post and as such I have difficulties understanding the question. A screenshot of said userform might be helpful. But essentially it seems to me that you are on the right track: if an option button is True then the sheet should be formatted (just like you have it in your code). — Ralph
– Ralph, Commented Apr 17, 2017 at 7:34
Thank you for updating the post. It is much easier to read now. In terms of your question it is difficult to answer / help as the relevant code is still missing. I cannot be sure as I cannot tell what's inside all of the other macros and especially since I cannot guess which OptionButton means what (e.g. what does OptionButton6 represent). But I am assuming that rowcolallsheetbcover will format all sheets. If you adjust the code for that sub to exclude whatever you have checked on your form then you are done. So, basically you'll have to edit the macro rowcolallsheetbcover (assumed). — Ralph
– Ralph, Commented Apr 17, 2017 at 9:38
I have added the rowcolallsheetbcover code as well and have modified it stating that if the sheet name is "Cover " column width and row height should change in all the sheets except "Cover" Sheet" — Stacey
– Stacey, Commented Apr 17, 2017 at 9:56
Inspite of modifying the rowcolallsheetbcover to exclude the "Cover" sheet still its not working — Stacey
– Stacey, Commented Apr 17, 2017 at 10:30

Ralph · Accepted Answer · 2017-04-17 13:04:14Z

1

I'd like to offer a slightly more concise approach to your problem. Here is the revised code for your CommandButton1_Click():

Option Explicit

Private Sub CommandButton1_Click()

Dim startColumn As Long
Dim formatAllSheets As Boolean
Dim sheetsToExcludeList As String

startColumn = 3
If Me.OptionButton5.Value Then startColumn = 2

formatAllSheets = True
If Me.OptionButton7.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 & ",Indexes"
sheetsToExcludeList = Mid(sheetsToExcludeList, 2)

Call FormatRowsAndColumns(formatAllSheets, startColumn, sheetsToExcludeList)

End Sub

And here is the adjusted (to the above calling) code for the module:

Option Base 1
Option Explicit
Option Compare Text

Sub FormatRowsAndColumns(formatAllSheets As Boolean, startColumn As Long, sheetsToExcludeList As String)

Dim sheetNumber As Long
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
                Call FormatThisSheet(startColumn, sheetNumber)
            End If
        Else
            Call FormatThisSheet(startColumn, sheetNumber)
        End If
    Next sheetNumber
Else
    Call FormatThisSheet(startColumn, ActiveSheet.Index)
End If

End Sub

Sub FormatThisSheet(startColumn As Long, sheetNumber As Long)

Dim lastRow As Long
Dim lastColumn As Long
Dim rangeToFormat As Range

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 = 9.4
    rangeToFormat.Cells.ColumnWidth = 11.2
End With

End Sub

Basically, the idea is that all of your subs seem to resemble one another because they do all (almost) the same thing with only minor changes. Hence, I decided to bring them all together into one sub. Of course, this means that the sub needs to know about the minor changes. Hence, I am calling the sub with parameters which tell the sub if all sheets should be formatted or only the active one, etc.

So, if you have Me.OptionButton5.Value being True then the starting column is column B. That's the second column on a sheet and hence I am passing startColumn = 2 to the sub. Otherwise, I am passing 3 to the sub (start with column C).

A similar approach is taken for the sheets. If you want to format all sheets then I am setting the boolean variable True otherwise I am setting it to false and pass it once again to the sub to format the appropriate sheets.

All the sheets you wish to exclude are being stored in a string variable. So, if you decide to exclude no sheet then sheetsToExcludeList will be empty sheetsToExcludeList = "". But if you decide to exclude Cover and Indexes then the variable becomes this sheetsToExcludeList = "Cover,Indexes".

The adjusted sub to format the sheets has been revised to cope with all these variables. Please have a look and do let me know if you have any questions.

edited Apr 17, 2017 at 13:04

answered Apr 17, 2017 at 12:42

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Stacey Over a year ago

Thank you so much for taking out time to resolve my issue. There are few doubts which I want to clarify First I didnt understand the below mentioned line: If LBound(sheetsToExcludeArray) <= UBound(sheetsToExcludeArray) Then If IsError(WorksheetFunction.Match(ThisWorkbook.Worksheets(sheetNumber).Name, sheetsToExcludeArray, 0)) Then

Stacey Over a year ago

Second If amongst the four checkboxes if I want to ensure that one of the checkboxes should exclude all the sheets ending with _Index so instead of "Index" can I write _Index in this line If Me.CheckBox4.Value Then sheetsToExcludeList = sheetsToExcludeList & ",Indexes"

Stacey Over a year ago

Thank you so much @Ralph, it would be really helpful if you can answer the above question or if I have to ask this question by adding a new question let me know I will do that

Ralph Over a year ago

Answer to your first question: this is to check if sheetsToExcludeList is empty and thus sheetsToExcludeArray turns out to be empty. If there is not sheet to exclude then all sheets should be processed. So, to check if no sheet should be skipped I am comparing the lower boundary LBound(sheetsToExcludeArray) to the uppper boundary UBound(sheetsToExcludeArray) of that array. If the lower boundary is 0 and the upper boundary is -1 then sheetsToExcludeArray is empty. Otherwise there is something in there (which we should skip).

Ralph Over a year ago

With the Match() function I am checking if the current sheet name is in the list of sheets to exclude. Here is how the Match() function works: support.office.com/en-us/article/… (on the sheet and in the VBA code alike).

|

Collectives™ on Stack Overflow

Run different macros based on userform checkbox values

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related