User Form

The code looks the same as VBA or VB, but most are done in parts. You may need conventional code to call the form, below is all the code to pull a user form, which must be built first and named UserForm1 it is a frame and one form button.

Unlike VB you use the utility to build a user form rather than the library function. Go to the Excel VB editor, Insert-UserFormn, this gives you a frame. The form toolbox should be visible if not pull it from the bar. Drag a button to the frame, edit the button properties for Caption and the like. The Right click the button, view code. Delete the click event automatically placed there and copy the code below. Note: the show userform code go's in the sheet module, this is the code you use to call the form. The button click event is where you place the code to call or run your macro code!

Sub myForm()
ActiveSheet.Select
UserForm1.Show
End Sub

This is the sheet code to pull the form it could be a hot-key or a form button on the worksheet, it go's in a sheet module.

Below is the codes required to make the form button on the user form change from red to green with a mouse over and or select and back to red when the focus is lost.

Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'On mouse-over, color form back Lt.Blue.
If UserForm1.BackColor = RGB(240, 240, 240) Then _
UserForm1.BackColor = RGB(220, 247, 247)
'On mouse-over, color Button Green.
If CommandButton1.BackColor <> RGB(122, 255, 0) Then _
CommandButton1.BackColor = RGB(122, 255, 100)
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'On mouse-over = gone, color form back Lt.Blue.
If UserForm1.BackColor = RGB(240, 240, 240) Then _
UserForm1.BackColor = RGB(220, 247, 247)
'On mouse-over = gone, color Button Red.
If CommandButton1.BackColor <> RGB(255, 0, 0) Then _
CommandButton1.BackColor = RGB(255, 0, 0)
End Sub

Private Sub UserForm_Activate()
'On form open color Back Lt.Blue.
UserForm1.BackColor = RGB(220, 247, 247)
'On form open color Button Bright Red.
If CommandButton1.BackColor <> RGB(255, 0, 0) Then _
CommandButton1.BackColor = RGB(255, 0, 0)
End Sub

Private Sub CommandButton1_Click()
'Deactivate UserForm & color back Lt.Gray.
UserForm1.BackColor = RGB(240, 240, 240)
'Show Msg.
MsgBox Chr(13) & Chr(13) & _
"This could have been any Visual Basic code or Macro!" _
& Chr(13) & Chr(13) & _
"The UserForm ""Button"" has activated this code." _
& Chr(13) & Chr(13) & _
"Any code can be placed here!" _
& Chr(13) & Chr(13) & Chr(13) & Chr(13) & _
" Press ""OK"" when done!" _
& Chr(13)
If UserForm1.BackColor = RGB(240, 240, 240) Then _
UserForm1.BackColor = RGB(220, 247, 247)
End Sub

Private Sub UserForm_Click()
'If UserForm Background is clicked, close form.
UserForm1.Hide
End Sub

Each code block accounts for a different event. Some action/response may need a code block to initialize another to be defined and another to activate all for one action. JSW