Message Box and Spreadsheet Functionality

[Blanket]

Hello!

I have a macro that asks the user through a message box to accept or reject the changes made by the macro. However, the excel sheet that needs to be reviewed to accept or reject those changes is fairly large and the user cannot see all the changes without scrolling though the page first. The problem is that the message box disables the functionality of the spreadsheet until you select one of the options ("Yes"/"No") making it impossible to scroll or navigate through the sheet. Is there a way around this?

Many thanks!

 

[Smitty]

Welcome to the Board!

You'll need to create a UserForm and set it to Modeless:

UserForm1.Show vbModeless

That way you can interact with the sheet while still having the form visible.

HTH,

 

[Blanket]

Hello Smitty,

Thanks so much for your responose! The UserForm does what I wanted it to do in terms of usability of the spreadsheet, however, I've lost the functionality of the buttons.

The buttons that I had in the messagebox didthe following:

No: Delete a particular column and exit the message box
Yes: Delete a different column and print "Last Update:" in cells(1,1); Date in cells(1,2); and exit message box

However, since now it seems like I have to enter separate subrutines for each command button, I do not know how to send the particular column number to each subrutine and having it update the sheet like it previously did. Another way that I thought of doing this is by having the userform return a different value to the main code depending on which command button is clicked, but I do not know how to do this either.

Do you have any suggestions on how I can do this?

again, thanks so much for your help,

Blanket

 

[Smitty]

Something like this:

Private Sub CommandButton1_Click()
  ActiveCell.EntireRow.Delete
End Sub

Private Sub CommandButton2_Click()
  Cells(1,1).Value = "Last Update:"
  Cells(1,2).Value = Date
End Sub