Preventing some code from running

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

I have some code that checks certain cells to limit input to those cells.
The procedure is initiated from ThisWorkbook and the code is in a public
module with the Worksheet_Change event in the worksheet module. The
procedure does what its supposed to do very well, except there are
occassions when the user should be allowed to input data that would normally
not comply.
The problem:
I am unable to stop the procedure from "doing its thing" unless I put a
breakpoint at the start of the procedure (either the public module or the
sheet module), which, of course brings up the debug dialog box.
My question is:
Is there a way to prevent the code from operating with some code that I
could run at such times? That is, is there some way to bypass/override code
in any way via some more code?
(Turning Calculation to manual does not work).
Rob
 
rob,

I'm sure there is.
Without seeing your code, it's difficult to give you a specific answer

but.......

Use a VBYesNo MsgBox in your code.
Something along the lines of "Do you really want to do this?"
If yes, let it happen and Exit your sub.
If no, just cancel the input.

John
 
Maybe you could set a flag when you don't want your macro to run. (Say A1 of a
hidden sheet???)


Then at the top of the code:

If lcase(thisworkbook.worksheets("myhiddensheet").range("a1").value) _
= "quitnow" then exit sub
'.... continue

I guess the question is: do you know when to toggle it on/off.
 
Thank you both, (David & John), I suspect that your suggestions are beyond
my expertise so if its not too much trouble, I've shown the code in question
below.
If you could help a novice with this then that would be surely appreciated.
I need to create a button on sheet4 that will stop the below procedure from
working, to allow the user to put in any number he chooses and then, alow
the procedure to operate as before after he has entered the number. As this
will be an infrequent occurence, I basically just want to have an Input box
open, (when the user clicks the button), to enter a number, (that does not
normally comply), so that the user can enter that number to just the one
active cell. He would be required to activate each cell (one at a time) and
click the button (to override) each time he wants to do this.
Could you also help me with.....I can create the button Ok but not too sure
how to get the input to be entered into the active cell.
Rob

In ThisWorkbook I have:
CheckValues

In Sheet4 module I have:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("G27:G1524")) Is Nothing Then _
CheckValues Target
End Sub

In a module called ValueChecks I have:
Public Static Sub CheckValues(Optional rCell As Range)
Dim vCheck As Variant
If IsEmpty(vCheck) Then
vCheck = Sheet4.Range("G27:G1524").Value
End If
If Not rCell Is Nothing Then
With rCell
If Not IsEmpty(.Value) Then
Application.EnableEvents = False
.Value = vCheck(.Row - 26, 1)
Application.EnableEvents = True
End If
End With
End If
End Sub
 
This might work for you:

I put a button from the Forms toolbar on the worksheet. I called it "Button 1".

I assigned it this macro:

Option Explicit
Sub TurnOffEvents()
Dim btn As Button
Set btn = ActiveSheet.Buttons(Application.Caller)
btn.Caption = "Pause Verification"
End Sub

Then in your worksheet_change event, I looked at the caption to see if I should
continue:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("G27:G1524")) Is Nothing Then
'do nothing
Else
With Me.Buttons("button 1")
If LCase(.Caption) = "pause verification" Then
'toggle the caption and get out
.Caption = "Verification Active"
Exit Sub
Else
CheckValues Target
End If
End With
End If

End Sub
 
rob,

Not enough here for me to figure out what you're trying to do.
Maybe Dave can (he's one of those that lives and breathes this stuff).
That was meant in a nice way Dave.
If you can't get this worked out in the ng, send me the file directly
and I'll do my best to help you get this working.

John

rob nobel said:
Thank you both, (David & John), I suspect that your suggestions are beyond
my expertise so if its not too much trouble, I've shown the code in question
below.
If you could help a novice with this then that would be surely appreciated.
I need to create a button on sheet4 that will stop the below procedure from
working, to allow the user to put in any number he chooses and then, alow
the procedure to operate as before after he has entered the number. As this
will be an infrequent occurence, I basically just want to have an Input box
open, (when the user clicks the button), to enter a number, (that does not
normally comply), so that the user can enter that number to just the one
active cell. He would be required to activate each cell (one at a time) and
click the button (to override) each time he wants to do this.
Could you also help me with.....I can create the button Ok but not too sure
how to get the input to be entered into the active cell.
Rob

In ThisWorkbook I have:
CheckValues

In Sheet4 module I have:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("G27:G1524")) Is Nothing Then _
CheckValues Target
End Sub

In a module called ValueChecks I have:
Public Static Sub CheckValues(Optional rCell As Range)
Dim vCheck As Variant
If IsEmpty(vCheck) Then
vCheck = Sheet4.Range("G27:G1524").Value
End If
If Not rCell Is Nothing Then
With rCell
If Not IsEmpty(.Value) Then
Application.EnableEvents = False
.Value = vCheck(.Row - 26, 1)
Application.EnableEvents = True
End If
End With
End If
End Sub



Dave Peterson said:
Maybe you could set a flag when you don't want your macro to run. (Say
Click to expand...
A1
of a
hidden sheet???)


Then at the top of the code:

If lcase(thisworkbook.worksheets("myhiddensheet").range("a1").value) _
= "quitnow" then exit sub
'.... continue

I guess the question is: do you know when to toggle it on/off.
Click to expand...
bypass/override
Click to expand...
 
Hi Dave and thanks.
With the code below, where exactly do I put that? After my
worksheet_change event or within that procedure somewhere?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("G27:G1524")) Is Nothing Then
'do nothing
Else
With Me.Buttons("button 1")
If LCase(.Caption) = "pause verification" Then
'toggle the caption and get out
.Caption = "Verification Active"
Exit Sub
Else
CheckValues Target
End If
End With
End If

End Sub

I note that when I clicked button 1, it's wording changes, but it never
changes back again. It may be because I don't think I've got the above
procedure happening yet??
If I have the full procedure, above, after my procedure and step through the
button code, it says, "Unable to get the Buttons property of the Worksheet
class".
Rob
 
Thanks any way John and for your offer. I will persist with Dave as he has
helped me many times before, but I do appreciate the fact that it's hard to
convey a problem in a way that makes it clear.
Rob

John Wilson said:
rob,

Not enough here for me to figure out what you're trying to do.
Maybe Dave can (he's one of those that lives and breathes this stuff).
That was meant in a nice way Dave.
If you can't get this worked out in the ng, send me the file directly
and I'll do my best to help you get this working.

John

rob nobel said:
Thank you both, (David & John), I suspect that your suggestions are beyond
my expertise so if its not too much trouble, I've shown the code in question
below.
If you could help a novice with this then that would be surely appreciated.
I need to create a button on sheet4 that will stop the below procedure from
working, to allow the user to put in any number he chooses and then, alow
the procedure to operate as before after he has entered the number. As this
will be an infrequent occurence, I basically just want to have an Input box
open, (when the user clicks the button), to enter a number, (that does not
normally comply), so that the user can enter that number to just the one
active cell. He would be required to activate each cell (one at a time) and
click the button (to override) each time he wants to do this.
Could you also help me with.....I can create the button Ok but not too sure
how to get the input to be entered into the active cell.
Rob

In ThisWorkbook I have:
CheckValues

In Sheet4 module I have:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("G27:G1524")) Is Nothing Then _
CheckValues Target
End Sub

In a module called ValueChecks I have:
Public Static Sub CheckValues(Optional rCell As Range)
Dim vCheck As Variant
If IsEmpty(vCheck) Then
vCheck = Sheet4.Range("G27:G1524").Value
End If
If Not rCell Is Nothing Then
With rCell
If Not IsEmpty(.Value) Then
Application.EnableEvents = False
.Value = vCheck(.Row - 26, 1)
Application.EnableEvents = True
End If
End With
End If
End Sub
Click to expand...
(Say
A1
of a
Click to expand...
lcase(thisworkbook.worksheets("myhiddensheet").range("a1").value)
_
Click to expand...
put
a or
the
Click to expand...
that
Click to expand...
 
You can only have one worksheet_change event per worksheet.

So this one replaces your existing one. (Notice that your original stuff still
gets called if things are ok: "Checkvalues Target")



rob said:
Hi Dave and thanks.
With the code below, where exactly do I put that? After my
worksheet_change event or within that procedure somewhere?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("G27:G1524")) Is Nothing Then
'do nothing
Else
With Me.Buttons("button 1")
If LCase(.Caption) = "pause verification" Then
'toggle the caption and get out
.Caption = "Verification Active"
Exit Sub
Else
CheckValues Target
End If
End With
End If

End Sub

I note that when I clicked button 1, it's wording changes, but it never
changes back again. It may be because I don't think I've got the above
procedure happening yet??
If I have the full procedure, above, after my procedure and step through the
button code, it says, "Unable to get the Buttons property of the Worksheet
class".
Rob
Click to expand...
 
Dave, I now understand that, but I can't figure the whole procedure out and
it does not work for me. At present, If I name the button "button 1", and
then click it, it changes to "Pause Verification". It never changes from
that to anything else on repeated clicks. (Not even to "Verification
Active" which I see is in your procedure.)
Furthermore, I can only enter data into the cell on the first instance of
clicking the button after opening the file, as further clicks do not do
anything at all.
Strangely though, whether the button macro exists or not and I open the VBE
and click the stop button therein, I can always enter data into any one of
those cells, but I need to click the stop button each time. So simply
stopping the procedure allows me to do what I need. I just can't get it to
stop using the macro you provide. Just to clarify, I've listed, below, what
the procedures are so far.
1. In ThisWorkbook:
Private Sub Workbook_Open()
CheckValues

2. In the worksheet (Sheet4) that this procedure needs to work in:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("G27:G1524")) Is Nothing Then _
CheckValues Target
End Sub

3. In a module named ValueChecks
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("G27:G1524")) Is Nothing Then
'do nothing
Else
With Me.Buttons("Button 1")
If LCase(.Caption) = "pause verification" Then
'toggle the caption and get out
.Caption = "Verification Active"
Exit Sub
Else
CheckValues Target
End If
End With
End If
Selection.Validation.ShowError = True
ActiveSheet.Protect
End Sub

Thanks for your help so far,
Rob
 
rob,
3. In a module named ValueChecks
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Click to expand...

That code has to be in a sheet module to work, not a regular module.
Right click on the sheet tab (the sheet that you want this to work in)
and select "View Code"
Cut and paste it there.

John

rob nobel said:
Dave, I now understand that, but I can't figure the whole procedure out and
it does not work for me. At present, If I name the button "button 1", and
then click it, it changes to "Pause Verification". It never changes from
that to anything else on repeated clicks. (Not even to "Verification
Active" which I see is in your procedure.)
Furthermore, I can only enter data into the cell on the first instance of
clicking the button after opening the file, as further clicks do not do
anything at all.
Strangely though, whether the button macro exists or not and I open the VBE
and click the stop button therein, I can always enter data into any one of
those cells, but I need to click the stop button each time. So simply
stopping the procedure allows me to do what I need. I just can't get it to
stop using the macro you provide. Just to clarify, I've listed, below, what
the procedures are so far.
1. In ThisWorkbook:
Private Sub Workbook_Open()
CheckValues

2. In the worksheet (Sheet4) that this procedure needs to work in:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("G27:G1524")) Is Nothing Then _
CheckValues Target
End Sub

3. In a module named ValueChecks
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("G27:G1524")) Is Nothing Then
'do nothing
Else
With Me.Buttons("Button 1")
If LCase(.Caption) = "pause verification" Then
'toggle the caption and get out
.Caption = "Verification Active"
Exit Sub
Else
CheckValues Target
End If
End With
End If
Selection.Validation.ShowError = True
ActiveSheet.Protect
End Sub

Thanks for your help so far,
Rob

Dave Peterson said:
You can only have one worksheet_change event per worksheet.

So this one replaces your existing one. (Notice that your original
Click to expand...
stuff
still
gets called if things are ok: "Checkvalues Target")
Click to expand...
through
the
Click to expand...
if
I
Click to expand...
number.
As
Click to expand...
the
one
Click to expand...
Then
_
Click to expand...
in
a
Click to expand...
module.
The
Click to expand...
unless
Click to expand...
 
And just add to what John wrote, the section that changes the button's caption
is in that worksheet_change procedure. And it gets toggled back whenever you
make that "free" change.



rob said:
Dave, I now understand that, but I can't figure the whole procedure out and
it does not work for me. At present, If I name the button "button 1", and
then click it, it changes to "Pause Verification". It never changes from
that to anything else on repeated clicks. (Not even to "Verification
Active" which I see is in your procedure.)
Furthermore, I can only enter data into the cell on the first instance of
clicking the button after opening the file, as further clicks do not do
anything at all.
Strangely though, whether the button macro exists or not and I open the VBE
and click the stop button therein, I can always enter data into any one of
those cells, but I need to click the stop button each time. So simply
stopping the procedure allows me to do what I need. I just can't get it to
stop using the macro you provide. Just to clarify, I've listed, below, what
the procedures are so far.
1. In ThisWorkbook:
Private Sub Workbook_Open()
CheckValues

2. In the worksheet (Sheet4) that this procedure needs to work in:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("G27:G1524")) Is Nothing Then _
CheckValues Target
End Sub

3. In a module named ValueChecks
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("G27:G1524")) Is Nothing Then
'do nothing
Else
With Me.Buttons("Button 1")
If LCase(.Caption) = "pause verification" Then
'toggle the caption and get out
.Caption = "Verification Active"
Exit Sub
Else
CheckValues Target
End If
End With
End If
Selection.Validation.ShowError = True
ActiveSheet.Protect
End Sub

Thanks for your help so far,
Rob
Click to expand...
 
Sorry to be such an ignoramis with this procedure but it has me completely
foxed.
I now have this in the worksheet module:

Sub TurnOffEvents()
Dim btn As Button
Set btn = ActiveSheet.Buttons(Application.Caller)
ActiveSheet.Unprotect
btn.Caption = "Pause Verification"
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("G27:G1524")) Is Nothing Then
'do nothing
Else
With Me.Buttons("Button 1")
If LCase(.Caption) = "pause verification" Then
'toggle the caption and get out
.Caption = "Verification Active"
Exit Sub
Else
CheckValues Target
End If
End With
End If
Selection.Validation.ShowError = True
ActiveSheet.Protect
End Sub

AND......this in an ordinary module
Public Static Sub CheckValues(Optional rCell As Range)
Dim vCheck As Variant
If IsEmpty(vCheck) Then
vCheck = Sheet4.Range("G27:G1524").Value
End If
If Not rCell Is Nothing Then
With rCell
If Not IsEmpty(.Value) Then
Application.EnableEvents = False
.Value = vCheck(.Row - 26, 1)
Application.EnableEvents = True
End If
End With
End If
End Sub

When I click the button nothing at all happens! (Except sometimes when I
first open the workbook and click the button I can hear some HardDrive
whirring going on for about 1or 2 seconds but not every time.)
If I go to VBE and debug (using F8), starting from Sub TurnOffEvents(), then
it comes up with an "Application defined or object defined error" message at
the line
Set btn = ActiveSheet.Buttons(Application.Caller)

I also cannot understand the line:
With Me.Buttons("Button 1")
as that does not ever seem to be the name on the button except if I
physically type that name into the button face??
 
First, did you use the button from the Forms toolbar (not the control toolbox
toolbar). There's a difference in their behavior.

(The application.caller will return the name of the button that called the
macro. If you step through the code, then the button didn't call the macro (you
did) and application.caller will return an error.)

me.buttons("button 1")

The me refers to thing holding the code. In this case, since it's behind the
worksheet, it refers to that worksheet.

And when you right click on the button (to select it), take a look at the
namebox (to the left of the formulabar). You'll see the name there. If you
only added one, then it should be "button 1".

If you added lots and deleted lots (minus 1), then you can change the name by
just typing in the new name in that namebox (with the button selected).

<<I was kind of afraid that you might have multiple buttons and I didn't want to
rely on something like:

me.buttons(1)

which would refer to the first button on the sheet.

====
It sounds like you were changing the caption (label on the button).
 
Yes Dave, I did use the forms toolbar button. Thanks for explaining some of
the code too.
When I check the name it says Button 69.
I tried to rename the button using that NameBox as you suggest, but it won't
let me change it to anything below number 69. I've renamed it Button 70 for
the time being as now I can't rename it back to 69. Can't work out why so
many numbers of buttons as I generally use the control toolbar button and
I've never used the forms button on this workbook.
So.....that part works OK and the button caption changes OK as per the code.
Problem:
If I click the button, it allows me to type in any number (which is as
intended).
If I then try to type in that number again, (or any other number), when the
button has not been clicked again, (that is, it's showing "Verification
active"), a previous number I typed, some time earlier, will appear.
In other words, I typed in a "4" at some stage and now, when I click the
button it allows me to type in the number "20", (which is good!). If I type
in "20" (or any other number into that same cell and the button is still in
"Verification active", the number "4" is shown again. How does this
procedure still remember earlier data input and how can that be avoided.
Could I also ask one more thing about this procedure?
The cells in that column all have data validation which prevent the user
putting in numbers that don't comply. This needs to be turned off
momentarily, for the cell into which the number is to be typed.
I tried inserting....
With Selection.Validation
.ShowError = False
End With
or even ....
Selection.Validation.ShowError = False
and then back to True at the end of the procedure, but I can't seem to make
that work properly each time. Sometimes it seems to work and other times I
get an error. Is that the correct wording, and where is the best place to
insert that command and return to trTrue within the procedure?

Thank you so much for your assistance.

Rob
 
Maybe you have 70 buttons on the worksheet????


If you can delete all the objects in the worksheet:

F5 (or edit|goto)
click special
check Objects
hit the delete key.

or from the VBE and run a small macro:
Option Explicit
Sub test()
Dim wks As Worksheet
Set wks = ActiveSheet
wks.Buttons.Delete
End Sub

Then add a single button back. (The name may not be "button 1", but you should
be able to rename it (assuming that you don't have any other object named
"button 1".
=========

In your CheckValues sub, you have a line that tells your macro to remember the
existing values in that range:

vCheck = Sheet4.Range("G27:G1524").Value

That sub looks like it was designed to put values back to the original if you
changed it.

===
And instead of turning off the data|validation, allowing the input, and then
turning it back on, you may just want to move all your data|validation rules to
your worksheet_change event.

You could change your data|validation so that it just issues a warning if the
entry is invalid. (This might be the easiest thing to do and trash everything
else???)

On the Data|validation dialog, there's an Error Alert Tab.

You can change it to just a warning--instead of stopping the user.

Why make them hit the button each time they want to break the rule--if you allow
them to break the rule anyway.
 
I deleted all the objects as you suggested and there were quite a few, but
nowhere near 70! (I did not realise that any objects like text boxes also
are considered when numbering the next forms button.)
After deleting all objects in all sheets, I saved the file and reopened it
and put another forms button on. The number showing is now 92??? (Yet on a
blank workbook the number is 1.)
I still can't rename it to anything below the number excel issues it with.
Anyway, unless you have an easy answer to this Dave, please don't waste any
time on this issue as it does not matter to me what number I give it. It is
just a matter of interest.
I'm more concerned with the operation and success of the code.
Thanks for your suggestion of...
"You could change your data|validation so that it just issues a warning if
the
entry is invalid. (This might be the easiest thing to do and trash
everything
else???)"
I did know of that, but I was so locked in with what had been suggested
before, I had overlooked that possibility, which I will give some more
thought to. My initual reason for going down the path I did was that Data
Validation can be deleted (such as copying and pasting other cells) and I
wanted a foolproof way to prevent this. But the complications in creating
some foolproof code that will accomodate the data validation as well is
getting beyond me and a less perfect way may be my only option.
That's because I feel your other suggestion .....
"to move all your data|validation rules to your worksheet_change event."
is something I'm going to need help on as well and I'm sure I've outstayed
my welcome and don't wish to bother you too much.
Many, many, thanks Dave for all your help, suggestions and effort to explain
some of the code,
Rob
 
When you get stuck, post back.

if you can run this line of code from a test macro:

activesheet.buttons("button 92").name = "Button 1"

I would think that you could change the name manually. Ahhhh. Did you hit
enter after you tried changing it in the name box???


and if you can't maybe:

Option Explicit
Sub testme01()

Dim btn As Button
Dim resp As Long
For Each btn In ActiveSheet.Buttons
resp = MsgBox(prompt:=btn.Name & " is near " _
& btn.TopLeftCell.Address(0, 0) _
& vbNewLine & "delete it?", Buttons:=vbYesNo)
If resp = vbYes Then
btn.Delete
End If
Next btn

End Sub
 
Oops! Sorry! You're right, I didn't press enter....just clicked somewhere
out of the box.
I can rename it to 1. Yahoo.
Strange though why such a huge number when I only have approx 15 text boxes
and 1 forms button. I've obviously created and deleted some along the way,
but how does Excel remember all that and why would that be something Excel
needs to remember? I see a similar thing when recording macros where the
next macro number is suggested as a name. But at least they get reset on
exiting Excel.
Never mind though....just "of interest" questions.
Rob
 
Back
Top