How to use UserForm to run the VBA code?

Mr.MJ 1 Reputation point


I use VBA to format my worksheet, but it is easy to get error. I want to use UserForm to run the VBA code. I made some adjustments also added some new features. The UserForm UI preview and relevant remarks as show below:



There are 3 buttons in total.

Clear button: click to clear all values for currently displayed page where in FRAME

Preview button: click to create a new sheet and then name as Preview, layout the format by the values from Textbox; if the Preview worksheet is existed, then to update the format if there are changed.

Apply to worksheet button: save first to avoid errors, then format the layout for the worksheet(s) if the name that contained with (FM) characters, such as sheet1(FM), (FM)sheet1 and sheet(FM)1; If the worksheet(s) is protected, then pop up MsgBox “please unprotect the worksheet(s) before you run this application. “


Require to enter the values to the Textboxs (as shown above - Red Notes). Depending on the content of the labels, the related properties are also different (as shown above - Green Notes)

Optional checkboxes – to make changes according to the content if it is clicked, as shown below:



There are 4 pages in total, each page contains Textbox. Depending on the content of the title, the related properties are also different.

  • If the Textbox is blank, the corresponding command does not make any changes
  • If there are two or more Textbox with values in the same group (as shown above - Purple Notes: Group 1, Group2 and Group3), then make changes based by values

Sample text checkboxes

Click to use cell style name to be a sample text and enter to the relevant cell (as shown above - Blue Notes))

Here is my file for reference。!AibaAz5STOvFiCLC5f9nMMdZvtAc?e=h9dUTS

How can I use VBA code to achieve the above functions please?

Many Thanks


Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,663 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,674 questions
0 comments No comments
{count} votes