Message Box Help - Basics

rjbinney

Active Member
Joined
Dec 20, 2010
Messages
250
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Reggie74

Board Regular
Joined
Sep 26, 2014
Messages
51
The Click() event of the button ought to trigger the display of the message box ;eg

Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
Maybe something like
Code:
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

Active Member
Joined
Dec 20, 2010
Messages
250
Office Version
  1. 365
Platform
  1. Windows
Thanks, both.

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

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

Reggie74

Board Regular
Joined
Sep 26, 2014
Messages
51
Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,806
Messages
5,524,987
Members
409,614
Latest member
wile2u

This Week's Hot Topics

Top