Need warning message before a user changes worksheet

  • Thread starter Thread starter JG Scott
  • Start date Start date
J

JG Scott

I have a worksheet called "Data". Anytime a user selects an entire
row I would like for a warning to appear that says "If you insert,
delete, or copy and insert rows in the top section, you must make the
same changes in the bottom section." Then, I want the user to be able
to click OK on the warning and proceed with whatever changes they
decide to make. The problem I have is how to keep the warning from
appearing again immediately when, for example, the user proceeds to
insert a row. Is there a timer I could use so the warning would not
occur again for some number of minutes?
 
I have a worksheet called "Data". Anytime a user selects an entire
row I would like for a warning to appear that says "If you insert,
delete, or copy and insert rows in the top section, you must make the
same changes in the bottom section." Then, I want the user to be able
to click OK on the warning and proceed with whatever changes they
decide to make. The problem I have is how to keep the warning from
appearing again immediately when, for example, the user proceeds to
insert a row. Is there a timer I could use so the warning would not
occur again for some number of minutes?
Click to expand...

Hi JG

Sub myWarning()
Dim WarnMess as integer
WarnMess = ("Danger Will Robinson, you are about to do something
monumentally Stupid! Are you sure you want to continue...", vbYesNo)
If WarnMess = vbNo then
Cancel = True
Else
'Do your thing here
End If
End Sub
 
Pick a cell on the worksheet to store a date & time. I will use cell
T1 to illustrate and assume the user will not try inserting a new row
1. Add the following to the worksheet's code window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 And Now - Range("T1") > 1 / 24 / 60 Then
MsgBox ("Your warning.")
Range("T1") = Now()
End If
End Sub

It will not warn again if a warning was given within the last minute.
 
JG Scott explained :
I have a worksheet called "Data". Anytime a user selects an entire
row I would like for a warning to appear that says "If you insert,
delete, or copy and insert rows in the top section, you must make the
same changes in the bottom section." Then, I want the user to be able
to click OK on the warning and proceed with whatever changes they
decide to make. The problem I have is how to keep the warning from
appearing again immediately when, for example, the user proceeds to
insert a row. Is there a timer I could use so the warning would not
occur again for some number of minutes?
Click to expand...

I suggest protecting the sheet and adding a menu or button that
appropriately automates the row insertion in both places.
 
Could be done if you can ensure that users would be selecting entire
rows.

That entirerow selection event can be trapped and a timer set to allow
entire rows to be selected and inserted for any period of time you
want before events are re-enabled and the warning re-triggered

But some events, like inserting a row via the insert row Icon or
right-click>insert>entire row or ctrl + shift++ are not trappable
events.

How would you deal with those?

How would you deal with the user who inserted the rows anyway but did
not insert them in the bottom section?

I think it may be easier to train your users.


Gord
 
Gord Dibben was thinking very hard :
I think it may be easier to train your users.
Click to expand...

But, Gord, you KNOW it would be better lock users out of messing with
the sheet structure AND automate the various processes! ...Right?<bg>
 
Back
Top