VBmsgbox to Userform

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
I have workbook that walks a user through about 17 different steps. Originally I set it up to use VBmsgboxes but an issue has come up there it would be easier if the user can still interact with the workbook ie userform via modeless. is their and easy way to convert msgbox syntax to intergrade with a userform. Also is there an easy way to have a progress bar change its value based on the number of marcos that still need to be run?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Yes, yes. But...

A VBmsgbox is nothing else than a userform with a label and a (few) button(s).

So you can create a userform with a label and a button (with caption Next)
1601911140229.png

Say that you currently have five instructions using the msgbox, then in the codemodule for the userform you make five subs (Instr1() - Instr5())
You transcribe each msgbox command to an instruction:
VBA Code:
Sub Instr1()
    Label1.Caption = "Go to cell G12 and enter date"
    CommandButton1.Caption = "Next"
    Me.Caption = "Enter party date"
    iI = 1
End Sub


Sub Instruction_Old_1()

    MsgBox prompt:="Go to cell G12 and enter date", _
           Title:="Enter party date"
           
End Sub

Then you add a Commandbutton1_Click() event to the subs:
VBA Code:
Private Sub CommandButton1_Click()
    Select Case iI
        Case 0
            Instr1
        Case 1
            Instr2
        Case 2
            Instr3
        Case 3
            Instr4
        Case 4
            Instr5
        Case 5
            Unload Me
     End Select
End Sub

Lastly you need to start this off. So on initialising the form, you tell it to 'press the button'.
VBA Code:
Private Sub UserForm_Initialize()
    CommandButton1_Click
End Sub

Then in a normal module, or in the workbook open module (where ever you start the messageboxes at the moment) you need to call the userform modeless.
VBA Code:
 Userform1.Show vbModeless

Get that running first, then you can add a progressbar.

By the way once you start using a userform, you can also use that to collect the input from the user. This allows you to check the input before the macro sticks it into the correct cell and goes to the next step. Or start minimising the number of times the user needs to press the button, by having several input fields and enabling the OK button only once all the fields have the necessary input. Endless opportunities...

As for the progressbar: a simple one can be made using two textboxes. the first textbox you leave in standard colours, and you stretch it across the userform. The second one gets a green background colour and same height, top and left as the first.
1601911417755.png


Now you need to change the commandbutton_click sub:
VBA Code:
Private Sub CommandButton1_Click()
    Const iProg = 5 '<< number of steps to be completed
    
    'set the progress bar
    TextBox2.Width = TextBox1.Width * (iI + 1) / iProg
    'select wich instruction to run
    Select Case iI
        Case 0
            Instr1
        Case 1
            Instr2
        Case 2
            Instr3
        Case 3
            Instr4
        Case 4
            Instr5
        Case 5
            Unload Me
    End Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,685
Messages
5,626,280
Members
416,171
Latest member
cfrenomaly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top