Help with worksheet_change script

[Clif McIrvin]

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]

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.

 

[programmernovice]

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.

 

[Clif McIrvin]

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<sp>search ]
and you get
[ <red>subsearch</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?

 

[programmernovice]

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

(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<sp>search ]
and you get
[ <red>subsearch</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?

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:=("collect.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]

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

(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<sp>search ]
and you get
[ <red>subsearch</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?

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:=("collect.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.)

 

[programmernovice]

"programmernovice" <[email protected]> wrote in message

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<sp>search ]
and you get
[ <red>subsearch</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:=("collect.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.)

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