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.

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.

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 14, 2:29*pm, GS wrote:
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.

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

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.

on 6/14/2011, programmernovice supposed :
On Jun 14, 2:29*pm, GS wrote:
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.

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

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.

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 14, 3:52*pm, GS wrote:
on 6/14/2011, programmernovice supposed :





On Jun 14, 2:29*pm, GS wrote:
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.

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

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.

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

Many thanks Garry, really apreciated it.

programmernovice formulated on Tuesday :
On Jun 14, 3:52*pm, GS wrote:
on 6/14/2011, programmernovice supposed :





On Jun 14, 2:29*pm, GS wrote:
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.

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
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.

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

Many thanks Garry, really apreciated it.

You're welcome! ..always glad to help!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 14, 3:52*pm, GS wrote:
on 6/14/2011,programmernovicesupposed :





On Jun 14, 2:29*pm, GS wrote:
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.

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

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.

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 -

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


On Fri, 17 Jun 2011 12:08:52 -0700 (PDT), 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.

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.

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

on 6/17/2011, Gord Dibben supposed :
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


On Fri, 17 Jun 2011 12:08:52 -0700 (PDT), 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.

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 17, 3:52*pm, GS wrote:
After serious thinkingprogrammernovicewrote :

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.

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

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 :
On Jun 17, 3:52*pm, GS wrote:
After serious thinkingprogrammernovicewrote :

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.

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

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.

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 20, 1:20*pm, GS wrote:
programmernovicehas brought this to us :





On Jun 17, 3:52 pm, GS wrote:
After serious thinkingprogrammernovicewrote :

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.

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

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.

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 -

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.

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[s] 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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 20, 3:56*pm, GS wrote:
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.

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[s] 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.

--
Garry


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 :
On Jun 20, 3:56*pm, GS wrote:
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.

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[s] 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.

--
Garry


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.

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
programmernovice laid this down on his screen :
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.

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.

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

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin presented the following explanation :
"GS" wrote in message ...
programmernovice laid this down on his screen :
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.

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.

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, 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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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.<?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
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.


Well doh!

Of course. the VBE would think subsearch is a yet-to-be-defined proc
name, and would throw a compile error, but not a syntax highlight. So
my post wasn't well thought through, either.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

"GS" wrote in message
...
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.<?



"incomplete" keypress was what I have generally experienced, as well (in
the case of the spacebar, sticky linkage) .... but as you pointed out
elsewhere that really shouldn't throw a syntax error.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Jun 21, 6:33*pm, "Clif McIrvin" wrote:
"GS" wrote in message

...





programmernovicelaid this down on his screen :
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.

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.

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

(clare reads his mail with moe, nomail feeds the bit bucket :-)- Hide quoted text -

- Show quoted text -

This is exactly what happens Clif. However, the spacebar always,
without fail, works everywhere else. Is simply does not register when
in the VB Editor. I guess I must have some bug in Excel.

"programmernovice" wrote in message
...
On Jun 21, 6:33 pm, "Clif McIrvin" wrote:
"GS" wrote in message

...





programmernovicelaid this down on his screen :
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.

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.

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

(clare reads his mail with moe, nomail feeds the bit bucket :-)- Hide
quoted text -

- Show quoted text -

This is exactly what happens Clif. However, the spacebar always,
without fail, works everywhere else. Is simply does not register when
in the VB Editor. I guess I must have some bug in Excel.

-----

I cannot reproduce the behavior you describe ...

Reading back through the thread, it sounds as though you were typing
Garry's code (instead of doing a copy + paste).

Some questions:

You say that (for instance) you enter [ sub<spsearch ]
and you get
[ <redsubsearch</red ].

But, you can insert a space between the b and s.

Question: is that always the case? In other words,
doeseverythingyoutypelooklikethis and not like this?

Can you copy / paste an example "in context" from your VBE window so
that we can see several lines above and below the line that is giving
you the syntax error (red) highlight?

I don't know if it matters, but what version of VBA are you running?
(Help|About from the VBE window).

What version of Excel?

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Jun 22, 10:41*am, "Clif McIrvin" wrote:
"programmernovice" wrote in message

...
On Jun 21, 6:33 pm, "Clif McIrvin" wrote:





"GS" wrote in message

...

programmernovicelaid this down on his screen :
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.

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.

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

(clare reads his mail with moe, nomail feeds the bit bucket :-)- Hide
quoted text -

- Show quoted text -

This is exactly what happens Clif. *However, the spacebar always,
without fail, works everywhere else. *Is simply does not register when
in the VB Editor. * *I guess I must have some bug in Excel.

-----

I cannot reproduce the behavior you describe ...

Reading back through the thread, it sounds as though you were typing
Garry's code (instead of doing a copy + paste).

Some questions:

You say that (for instance) you enter [ sub<spsearch ]
and you get
[ <redsubsearch</red ]

When I type it, "sub" comes out red. After <sp the whole thing
turns black. So I end up with subsearch in black.

But, you can insert a space between the b and s.

Question: is that always the case? In other words,
doeseverythingyoutypelooklikethis and not like this?

that is always the case.


Can you copy / paste an example "in context" from your VBE window so
that we can see several lines above and below the line that is giving
you the syntax error (red) highlight?

subdifmin()

solveroksetcell:=Range("Collect.xlm!
absDif"),maxminval:=2,valueof:="0",bychange:=("col lect.xlm!
absdif").offset(0,-1)
solversolve (True)
solverfinish (True)

EndSub

All the above was typed in, with spacebar in the appropriate places
(e.g. between End & Sub). Interestingly only the first 2 lines are in
red, the rest are black. I could, if I wanted to, separate EndSub by
placing the cursor between d and S and hitting spacebar.



I don't know if it matters, but what version of VBA are you running?
(Help|About from the VBE window).

What version of Excel?

Excel is 2003 SP3

Microsoft VB is 6.5 version 1053.

This problem is more of a nuisance than anything else, I can always
fix it as described, but it would be nice not to have to.
Thanks for your kind help.





--
Clif McIrvin

"programmernovice" wrote in message
...
On Jun 22, 10:41 am, "Clif McIrvin" wrote:
"programmernovice" wrote in message

...
On Jun 21, 6:33 pm, "Clif McIrvin" wrote:





"GS" wrote in message

...

programmernovicelaid this down on his screen :
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.

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.

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

(clare reads his mail with moe, nomail feeds the bit bucket :-)-
Hide
quoted text -

- Show quoted text -

This is exactly what happens Clif. However, the spacebar always,
without fail, works everywhere else. Is simply does not register when
in the VB Editor. I guess I must have some bug in Excel.

-----

I cannot reproduce the behavior you describe ...

Reading back through the thread, it sounds as though you were typing
Garry's code (instead of doing a copy + paste).

Some questions:

You say that (for instance) you enter [ sub<spsearch ]
and you get
[ <redsubsearch</red ]

When I type it, "sub" comes out red. After <sp the whole thing
turns black. So I end up with subsearch in black.

But, you can insert a space between the b and s.

Question: is that always the case? In other words,
doeseverythingyoutypelooklikethis and not like this?

that is always the case.


Can you copy / paste an example "in context" from your VBE window so
that we can see several lines above and below the line that is giving
you the syntax error (red) highlight?

subdifmin()

solveroksetcell:=Range("Collect.xlm!
absDif"),maxminval:=2,valueof:="0",bychange:=("col lect.xlm!
absdif").offset(0,-1)
solversolve (True)
solverfinish (True)

EndSub

All the above was typed in, with spacebar in the appropriate places
(e.g. between End & Sub). Interestingly only the first 2 lines are in
red, the rest are black. I could, if I wanted to, separate EndSub by
placing the cursor between d and S and hitting spacebar.



I don't know if it matters, but what version of VBA are you running?
(Help|About from the VBE window).

What version of Excel?

Excel is 2003 SP3

Microsoft VB is 6.5 version 1053.

This problem is more of a nuisance than anything else, I can always
fix it as described, but it would be nice not to have to.
Thanks for your kind help.

----------

I'd consider that much more than merely "a nuisance"! I've never heard
of anything like what you just described ... the additional detail you
provided makes it much easier (for me, at least!) to realize what you
are seeing at your end.

If no-one else "bites" on this thread in the next day or so, I'd suggest
that you re-post this explanation under a subject line something like,
"<spacebar ignored by VBE" and see if you get a response from others.

Maybe try to "repair" your Office installation? (Others in this room
have much more experience than I.)

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Jun 22, 3:09*pm, "Clif McIrvin" wrote:
"programmernovice" wrote in message

...
On Jun 22, 10:41 am, "Clif McIrvin" wrote:





"programmernovice" wrote in message

....
On Jun 21, 6:33 pm, "Clif McIrvin" wrote:

"GS" wrote in message

...

programmernovicelaid this down on his screen :
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.

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.

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

(clare reads his mail with moe, nomail feeds the bit bucket :-)-
Hide
quoted text -

- Show quoted text -

This is exactly what happens Clif. However, the spacebar always,
without fail, works everywhere else. Is simply does not register when
in the VB Editor. I guess I must have some bug in Excel.

-----

I cannot reproduce the behavior you describe ...

Reading back through the thread, it sounds as though you were typing
Garry's code (instead of doing a copy + paste).

Some questions:

You say that (for instance) you enter [ sub<spsearch ]
and you get
[ <redsubsearch</red ]

When I type it, *"sub" comes out red. *After <sp the whole thing
turns black. *So I end up with subsearch in black.



But, you can insert a space between the b and s.

Question: is that always the case? In other words,
doeseverythingyoutypelooklikethis and not like this?

that is always the case.



Can you copy / paste an example "in context" from your VBE window so
that we can see several lines above and below the line that is giving
you the syntax error (red) highlight?

subdifmin()

solveroksetcell:=Range("Collect.xlm!
absDif"),maxminval:=2,valueof:="0",bychange:=("col lect.xlm!
absdif").offset(0,-1)
solversolve (True)
solverfinish (True)

EndSub

All the above was typed in, with spacebar in the appropriate places
(e.g. between End & Sub). *Interestingly only the first 2 lines are in
red, the rest are black. *I could, if I wanted to, separate EndSub by
placing the cursor between d and S and hitting spacebar.



I don't know if it matters, but what version of VBA are you running?
(Help|About from the VBE window).

What version of Excel?

Excel is 2003 SP3

Microsoft VB is 6.5 version 1053.

This problem is more of a nuisance than anything else, I can always
fix it as described, but it would be nice not to have to.
Thanks for your kind help.

----------

I'd consider that much more than merely "a nuisance"! *I've never heard
of anything like what you just described ... the additional detail you
provided makes it much easier (for me, at least!) to realize what you
are seeing at your end.

If no-one else "bites" on this thread in the next day or so, I'd suggest
that you re-post this explanation under a subject line something like,
"<spacebar ignored by VBE" and see if you get a response from others.

Maybe try to "repair" your Office installation? (Others in this room
have much more experience than I.)

--
Clif McIrvin

OK Clif, many thanks for helping me out with this.