Help with worksheet_change script

  • Thread starter Thread starter lousypianist
  • Start date Start date
L

lousypianist

I am trying to write a script starting with

Private Sub Worksheet_Change(ByVal Target as Range)

To save the worksheet when any cell in a range is changed. However,
after the script is put into the VB code section, somehow the macro
does not appear in the list of macros for the particular worksheet.
Somehow my script is not recognized as a macro. Can someone please
tell me what I have to do to make sure the thing appears as a macro.
Many thanks in advance.
 
(e-mail address removed) explained :
I am trying to write a script starting with

Private Sub Worksheet_Change(ByVal Target as Range)

To save the worksheet when any cell in a range is changed. However,
after the script is put into the VB code section, somehow the macro
does not appear in the list of macros for the particular worksheet.
Somehow my script is not recognized as a macro. Can someone please
tell me what I have to do to make sure the thing appears as a macro.
Many thanks in advance.
Click to expand...

Worksheet event procedures will never appear in the Macros dialog
because they aren't executable that way. Events respond to user actions
in the UI or by VBA code if the procedure works such that it causes a
worksheet event to fire.
 
(e-mail address removed) explained :




Worksheet event procedures will never appear in the Macros dialog
because they aren't executable that way. Events respond to user actions
in the UI or by VBA code if the procedure works such that it causes a
worksheet event to fire.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

OK Garry many thanks. Suppose I wanted the worksheet to be saved if
some cell in A1:A5 is changed. What would the script look like, and
how would it be triggered. Thanks for helping my out.
 
OK Garry many thanks. Suppose I wanted the worksheet to be saved if
some cell in A1:A5 is changed. What would the script look like, and
how would it be triggered. Thanks for helping my out.
Click to expand...

Try...

Private Sub Worksheet_Change(ByVal Target as Range)
If Not Intersect(Target, Range("$A$1:$A$5")) Is Nothing Then _
ThisWorkbook.Save
End Sub
 
Try...

Private Sub Worksheet_Change(ByVal Target as Range)
  If Not Intersect(Target, Range("$A$1:$A$5")) Is Nothing Then _
    ThisWorkbook.Save
End Sub
Click to expand...
Many thanks Garry, really apreciated it.
 
Try...

Private Sub Worksheet_Change(ByVal Target as Range)
  If Not Intersect(Target, Range("$A$1:$A$5")) Is Nothing Then _
    ThisWorkbook.Save
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Garry, unfortunately after putting this in nothing happens. I
suspect it has to do with the definition of "target", I am defining a
range of cells in the worksheet as "target", but apparently this
doesn't help. Any ideas? Thanks again for your patience.
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$A$1:$A$5")) Is Nothing Then
ThisWorkbook.Save
End If
End Sub


Gord Dibben MS Excel MVP
 
After serious thinking programmernovice wrote :
Hi Garry, unfortunately after putting this in nothing happens. I
suspect it has to do with the definition of "target", I am defining a
range of cells in the worksheet as "target", but apparently this
doesn't help. Any ideas? Thanks again for your patience.
Click to expand...

In this event handler, 'Target' holds a ref to the cell that changed.
What the code does is to check if that cell is within your criteria
Range("$A$1:$A$5"). You'll need to revise this ("$A$1:$A$5") to be the
range where you want to monitor for any changes made to its cells.
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$A$1:$A$5")) Is Nothing Then
ThisWorkbook.Save
End If
End Sub


Gord Dibben MS Excel MVP
Click to expand...

Gord, I don't think the line continuation is the problem but better to
not include it I guess!<g>
 
After serious thinkingprogrammernovicewrote :


In this event handler, 'Target' holds a ref to the cell that changed.
What the code does is to check if that cell is within your criteria
Range("$A$1:$A$5"). You'll need to revise this ("$A$1:$A$5") to be the
range where you want to monitor for any changes made to its cells.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Thanks again, Garry. Somehow Excel doesn't seem to recognize the
event. I entered the script (without the line continuation) just as
you indicated. Cells $A$1:$A$5 were empty. I entered "23" in A3,
but nothing happened. Any idea what may be happening? Sorry to keep
bugging about this, you obviously have better things to do.
 
programmernovice has brought this to us :
Thanks again, Garry. Somehow Excel doesn't seem to recognize the
event. I entered the script (without the line continuation) just as
you indicated. Cells $A$1:$A$5 were empty. I entered "23" in A3,
but nothing happened. Any idea what may be happening? Sorry to keep
bugging about this, you obviously have better things to do.
Click to expand...

Did you put the code behind the sheet? Or did you put it somewhere
else?

To put the code behind the sheet, right-click the sheet tab and select
'View code' from the context menu. The event procedure should be what
you see. If not then you've put it in the wrong place so go do a
cut/paste to get it where it should be.
 
programmernovicehas brought this to us :







Did you put the code behind the sheet? Or did you put it somewhere
else?

To put the code behind the sheet, right-click the sheet tab and select
'View code' from the context menu. The event procedure should be what
you see. If not then you've put it in the wrong place so go do a
cut/paste to get it where it should be.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

That did it, thanks so much Garry. One last question (for the
moment!): When writing the code the space bar doesn't work, the words
(in red) have to be separated by inserting the cursor between them and
then hitting the spacebar. Any way to avoid this? Thanks again for
your patience.
 
programmernovice formulated the question :
That did it, thanks so much Garry. One last question (for the
moment!): When writing the code the space bar doesn't work, the words
(in red) have to be separated by inserting the cursor between them and
then hitting the spacebar. Any way to avoid this? Thanks again for
your patience.
Click to expand...

You're welcome.
I'm not sure what it is you're describing about words in red. That only
happens when there's syntax error in code. I wrote it as a single
line If..Then construct, which included the line continuation character
(underscore) so that 'ThisWorkbook.Save' was on a separate line. You
can backspace that 2nd line right up until the underscore is gone so it
reads on 1 line rather than being split into 2 lines with the
continuation character.
 
programmernoviceformulated the question :
That did it, thanks so much Garry.  One last question (for the
moment!):  When writing the code the space bar doesn't work, the words
(in red) have to be separated by inserting the cursor between them and
then hitting the spacebar.  Any way to avoid this?  Thanks again for
your patience.
Click to expand...

You're welcome.
I'm not sure what it is you're describing about words in red. That only
happens when there's syntax error in code. I wrote it as a single
line If..Then construct, which included the line continuation character
(underscore) so that 'ThisWorkbook.Save' was on a separate line. You
can backspace that 2nd line right up until the underscore is gone so it
reads on 1 line rather than being split into 2 lines with the
continuation character.
Click to expand...



Let's say I type

sub
then I press the space bar
and then type search

I get subsearch, all letters in red. If I then insert the cursor
after "b" and press the space bar I get

sub search, now all letters black. Any idea what may be causing this,
some setting in the VB Editor,perhaps? Thanks again.
 
programmernovice laid this down on his screen :
programmernoviceformulated the question :
That did it, thanks so much Garry.  One last question (for the
moment!):  When writing the code the space bar doesn't work, the words
(in red) have to be separated by inserting the cursor between them and
then hitting the spacebar.  Any way to avoid this?  Thanks again for
your patience.
Click to expand...

You're welcome.
I'm not sure what it is you're describing about words in red. That only
happens when there's syntax error in code. I wrote it as a single
line If..Then construct, which included the line continuation character
(underscore) so that 'ThisWorkbook.Save' was on a separate line. You
can backspace that 2nd line right up until the underscore is gone so it
reads on 1 line rather than being split into 2 lines with the
continuation character.
Click to expand...



Let's say I type

sub
then I press the space bar
and then type search

I get subsearch, all letters in red. If I then insert the cursor
after "b" and press the space bar I get

sub search, now all letters black. Any idea what may be causing this,
some setting in the VB Editor,perhaps? Thanks again.
Click to expand...


This is the normal behavior for the VB Editor. It's a built-in feature
to help guide you in writing code using proper syntax.

'subsearch' is not a keyword, and needs to be defined somewhere or it
will turn red to indicate you have typed improper code syntax.

'sub search' remains black because VBA thinks you are create a Sub
procedure named "search".

If you want to insert comments, prepend each comment line with an
apostrophe. Note that commented text/lines turn green in the VB Editor.

Example:
'this is a commented line
On Error Resume Next '//this is commented text. Execution stops at
the
apostrophe.
 
GS said:
programmernovice laid this down on his screen :

This is the normal behavior for the VB Editor. It's a built-in feature
to help guide you in writing code using proper syntax.
Click to expand...

Well, yes ... and no. If you read the question again, OP pressed the
spacebar between sub and search but the space character didn't register.

Sounds to me like a spacebar that only works part of the time.
(I have seen that from time to time over the years.)
 
Clif McIrvin presented the following explanation :
Well, yes ... and no. If you read the question again, OP pressed the spacebar
between sub and search but the space character didn't register.

Sounds to me like a spacebar that only works part of the time.
(I have seen that from time to time over the years.)
Click to expand...

Clif, if you try this as explained by the OP, you'll find the behavior
exactly as I stated. The spacebar seems to be working as expected. What
i suspect is the OP is not pressing Enter after typing 'sub search',
but rather just using right/down arrow to advance to the next line.
 
Just to clarify...

I often hit keys that don't produce anything because I'm not pressing
the key correctly. This has to do with me having Lou Gehrig's, but it's
not uncommon for folks who type quickly to "think" they pressed a key
while typing, but actually did not effect a 'keypress' on the keyboard.

I'd agree with you if this was happening elsewhere, but OP says it only
happens in VB Editor.<?>
 
And here is where I *EAT MY WORDS!!*

It seems the syntax error color will only have effect if the text
'appears' executable, but isn't. Also, typing 'subsearch' by itself
anywhere remains black after advancing the caret. Typing 'sub search'
and advancing the caret results in creating an empty procedure.

That concludes, then, that there's an anomoly at work with the OP's
VBE.
 
Back
Top