Message Box Help - Basics

[rjbinney]

I need to write a macro that inserts a row, and some formulas, BELOW a specific row.

Since the "Insert Row" command adds a row ABOVE the selected row, I would like to:
1) Have a button that says "Insert Row"
2) Have a message box pop up that says something to the effect of, "Make sure you have selected a cell in the correct row", with the options something like, "OK - Go ahead" or "Whoops - Cancel"
3) Then, when they click "OK - Go ahead", it runs the macro.

I've Googled message boxes, and I see the VBA code.

What I don't know is, what actually triggers the display of the message box?

 

[Reggie74]

The Click() event of the button ought to trigger the display of the message box ;eg

Sub Insert Row_Click()
    MsgBox "Make sure you have selected a cell in the correct row", vbOKCancel, "Go ahead / Cancel ?"

End Sub

I need to write a macro that inserts a row, and some formulas, BELOW a specific row.

Since the "Insert Row" command adds a row ABOVE the selected row, I would like to:
1) Have a button that says "Insert Row"
2) Have a message box pop up that says something to the effect of, "Make sure you have selected a cell in the correct row", with the options something like, "OK - Go ahead" or "Whoops - Cancel"
3) Then, when they click "OK - Go ahead", it runs the macro.

I've Googled message boxes, and I see the VBA code.

What I don't know is, what actually triggers the display of the message box?

 

[Michael M]

Maybe something like

Sub MM1()
Dim ar As Range
Set ar = Application.InputBox("Please Select a cell to Insert a row", "Insert Row", Type:=8)
Rows(ar.Offset(-1, 0).Row).Insert
End Sub

 

[rjbinney]

Thanks, both.

Reggie - Whether or not I hit "OK" or "Cancel", it inserts the row anyway.

Michael - what does "Type:=8" mean?

 

[Reggie74]

Sub Insert Row_Click()
    MsgBox "Make sure you have selected a cell in the correct row", vbOKCancel, "Go ahead / Cancel ?"


    'you will have to 'tell' the macro what to do when the user clicks on either "OK" or "CANCEL" 
    'eg; 
    
     if vbYes then
        ' Do this
     elseif vbCancel then
        ' Do something else 
     end if
		
End Sub

Thanks, both.

Reggie - Whether or not I hit "OK" or "Cancel", it inserts the row anyway.

Michael - what does "Type:=8" mean?

 

[Michael M]

Type 8 means the user has to select a cell, rather than type in a value
See here for further infomation


Application.InputBox Method (Excel)