VBmsgbox to Userform

[Jake975]

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?

 

[sijpie]

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)

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:

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:

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'.

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.

 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.

Now you need to change the commandbutton_click sub:

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