Help pls! I need a procedure which can help me to select a selection A and B
respectively, so that selection A is chosen every other time the procedure
runs (and the same for selection B). I intended to make a procedure that sets
the flag to TRUE when the flag is FALSE and vice versa. In the code presented
below, the boolean value remains FALSE and never changes to TRUE.

What I am doing wrong??

Public Sub Insert_Flag()
Dim Worksheet As Worksheet
Dim Flg As Boolean

Set Worksheet = ActiveSheet

Select Case Flg

Case Flg = True (Selection A)
Flg = False
If Flg = False Then Cells(1, 3) = False

Case Flg = False (Selection B)
Flg = True
If Flg = True Then Cells(1, 3) = True

End Select

End Sub

Use

Static Flg as Boolean

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Kragelund" <(E-Mail Removed)> wrote in message news:2629FAFA-AA30-4F0A-B870-(E-Mail Removed)...
| Help pls! I need a procedure which can help me to select a selection A and B
| respectively, so that selection A is chosen every other time the procedure
| runs (and the same for selection B). I intended to make a procedure that sets
| the flag to TRUE when the flag is FALSE and vice versa. In the code presented
| below, the boolean value remains FALSE and never changes to TRUE.
|
| What I am doing wrong??
|
| Public Sub Insert_Flag()
| Dim Worksheet As Worksheet
| Dim Flg As Boolean
|
| Set Worksheet = ActiveSheet
|
| Select Case Flg
|
| Case Flg = True (Selection A)
| Flg = False
| If Flg = False Then Cells(1, 3) = False
|
| Case Flg = False (Selection B)
| Flg = True
| If Flg = True Then Cells(1, 3) = True
|
| End Select
|
| End Sub

You have declared Flg to be a local (private) variable to the
procedure. You need a public variable. In a general code module,
outside of any sub or function, have the line

Public Flg as Boolean

and delete the line

Dim Flg as Boolean

from your code. This will toggle back and forth, It will start false
when you open Excel or whenever you reset the project. If you want
persistence across sessions you need to do something like hide the flag
in an out-of-the-way cell or something fancier like a registry setting
or entry in the names collection.

HTH

-John Coleman

Kragelund wrote:
> Help pls! I need a procedure which can help me to select a selection A and B
> respectively, so that selection A is chosen every other time the procedure
> runs (and the same for selection B). I intended to make a procedure that sets
> the flag to TRUE when the flag is FALSE and vice versa. In the code presented
> below, the boolean value remains FALSE and never changes to TRUE.
>
> What I am doing wrong??
>
> Public Sub Insert_Flag()
> Dim Worksheet As Worksheet
> Dim Flg As Boolean
>
> Set Worksheet = ActiveSheet
>
> Select Case Flg
>
> Case Flg = True (Selection A)
> Flg = False
> If Flg = False Then Cells(1, 3) = False
>
> Case Flg = False (Selection B)
> Flg = True
> If Flg = True Then Cells(1, 3) = True
>
> End Select
>
> End Sub

Dear John & Niek,

thanks a bunch for your input, I had no idea I needed to declare a static
variable. I did as you suggested, John, but unfortunately, the procedure
still doesn't toggle anywhere away from FALSE. Could there be any other
points I should take into consideration? I am running the procedure from
general code module (not a worksheet one).

Thanks in advance
Henrik


"John Coleman" wrote:

> You have declared Flg to be a local (private) variable to the
> procedure. You need a public variable. In a general code module,
> outside of any sub or function, have the line
>
> Public Flg as Boolean
>
> and delete the line
>
> Dim Flg as Boolean
>
> from your code. This will toggle back and forth, It will start false
> when you open Excel or whenever you reset the project. If you want
> persistence across sessions you need to do something like hide the flag
> in an out-of-the-way cell or something fancier like a registry setting
> or entry in the names collection.
>
> HTH
>
> -John Coleman
>
> Kragelund wrote:
> > Help pls! I need a procedure which can help me to select a selection A and B
> > respectively, so that selection A is chosen every other time the procedure
> > runs (and the same for selection B). I intended to make a procedure that sets
> > the flag to TRUE when the flag is FALSE and vice versa. In the code presented
> > below, the boolean value remains FALSE and never changes to TRUE.
> >
> > What I am doing wrong??
> >
> > Public Sub Insert_Flag()
> > Dim Worksheet As Worksheet
> > Dim Flg As Boolean
> >
> > Set Worksheet = ActiveSheet
> >
> > Select Case Flg
> >
> > Case Flg = True (Selection A)
> > Flg = False
> > If Flg = False Then Cells(1, 3) = False
> >
> > Case Flg = False (Selection B)
> > Flg = True
> > If Flg = True Then Cells(1, 3) = True
> >
> > End Select
> >
> > End Sub
>
>

By all means go the static approach if it works for you. It is probably
a cleaner approach unless other procedures need access to the same
flag. Still - the Public variable approach should work. I just tried
the following code to check my sanity (which is often an open
question):

Public Flg As Boolean

Public Sub Insert_Flag()

Select Case Flg
Case True
Flg = False
If Flg = False Then Cells(1, 3) = False
Case False
Flg = True
If Flg = True Then Cells(1, 3) = True

End Select
End Sub

It toggles back and forth for me with no problem.

Are you sure that you declared the public variable *outside* the
procedure? If you did, are you sure that you eliminated the local
variable declaration *inside* the procedure as well? Any local
declaration would hide any global declaration within the scope of the
procedure.

-John Coleman

Kragelund wrote:
> Dear John & Niek,
>
> thanks a bunch for your input, I had no idea I needed to declare a static
> variable. I did as you suggested, John, but unfortunately, the procedure
> still doesn't toggle anywhere away from FALSE. Could there be any other
> points I should take into consideration? I am running the procedure from
> general code module (not a worksheet one).
>
> Thanks in advance
> Henrik
>
>
> "John Coleman" wrote:
>
> > You have declared Flg to be a local (private) variable to the
> > procedure. You need a public variable. In a general code module,
> > outside of any sub or function, have the line
> >
> > Public Flg as Boolean
> >
> > and delete the line
> >
> > Dim Flg as Boolean
> >
> > from your code. This will toggle back and forth, It will start false
> > when you open Excel or whenever you reset the project. If you want
> > persistence across sessions you need to do something like hide the flag
> > in an out-of-the-way cell or something fancier like a registry setting
> > or entry in the names collection.
> >
> > HTH
> >
> > -John Coleman
> >
> > Kragelund wrote:
> > > Help pls! I need a procedure which can help me to select a selection A and B
> > > respectively, so that selection A is chosen every other time the procedure
> > > runs (and the same for selection B). I intended to make a procedure that sets
> > > the flag to TRUE when the flag is FALSE and vice versa. In the code presented
> > > below, the boolean value remains FALSE and never changes to TRUE.
> > >
> > > What I am doing wrong??
> > >
> > > Public Sub Insert_Flag()
> > > Dim Worksheet As Worksheet
> > > Dim Flg As Boolean
> > >
> > > Set Worksheet = ActiveSheet
> > >
> > > Select Case Flg
> > >
> > > Case Flg = True (Selection A)
> > > Flg = False
> > > If Flg = False Then Cells(1, 3) = False
> > >
> > > Case Flg = False (Selection B)
> > > Flg = True
> > > If Flg = True Then Cells(1, 3) = True
> > >
> > > End Select
> > >
> > > End Sub
> >
> >

John,

Given the state of my own wet matter, I don't question your sanity. Your
version works fine. My problem here was that I had defined Case Flg = True,
which didn't work, whereas you defined it Case True which works perfectly. As
you may have guessed I'm a newbie.

Many thanks, you saved me quite a number of my valuable few remaining hairs.

Henrik


"John Coleman" wrote:

> By all means go the static approach if it works for you. It is probably
> a cleaner approach unless other procedures need access to the same
> flag. Still - the Public variable approach should work. I just tried
> the following code to check my sanity (which is often an open
> question):
>
> Public Flg As Boolean
>
> Public Sub Insert_Flag()
>
> Select Case Flg
> Case True
> Flg = False
> If Flg = False Then Cells(1, 3) = False
> Case False
> Flg = True
> If Flg = True Then Cells(1, 3) = True
>
> End Select
> End Sub
>
> It toggles back and forth for me with no problem.
>
> Are you sure that you declared the public variable *outside* the
> procedure? If you did, are you sure that you eliminated the local
> variable declaration *inside* the procedure as well? Any local
> declaration would hide any global declaration within the scope of the
> procedure.
>
> -John Coleman
>
> Kragelund wrote:
> > Dear John & Niek,
> >
> > thanks a bunch for your input, I had no idea I needed to declare a static
> > variable. I did as you suggested, John, but unfortunately, the procedure
> > still doesn't toggle anywhere away from FALSE. Could there be any other
> > points I should take into consideration? I am running the procedure from
> > general code module (not a worksheet one).
> >
> > Thanks in advance
> > Henrik
> >
> >
> > "John Coleman" wrote:
> >
> > > You have declared Flg to be a local (private) variable to the
> > > procedure. You need a public variable. In a general code module,
> > > outside of any sub or function, have the line
> > >
> > > Public Flg as Boolean
> > >
> > > and delete the line
> > >
> > > Dim Flg as Boolean
> > >
> > > from your code. This will toggle back and forth, It will start false
> > > when you open Excel or whenever you reset the project. If you want
> > > persistence across sessions you need to do something like hide the flag
> > > in an out-of-the-way cell or something fancier like a registry setting
> > > or entry in the names collection.
> > >
> > > HTH
> > >
> > > -John Coleman
> > >
> > > Kragelund wrote:
> > > > Help pls! I need a procedure which can help me to select a selection A and B
> > > > respectively, so that selection A is chosen every other time the procedure
> > > > runs (and the same for selection B). I intended to make a procedure that sets
> > > > the flag to TRUE when the flag is FALSE and vice versa. In the code presented
> > > > below, the boolean value remains FALSE and never changes to TRUE.
> > > >
> > > > What I am doing wrong??
> > > >
> > > > Public Sub Insert_Flag()
> > > > Dim Worksheet As Worksheet
> > > > Dim Flg As Boolean
> > > >
> > > > Set Worksheet = ActiveSheet
> > > >
> > > > Select Case Flg
> > > >
> > > > Case Flg = True (Selection A)
> > > > Flg = False
> > > > If Flg = False Then Cells(1, 3) = False
> > > >
> > > > Case Flg = False (Selection B)
> > > > Flg = True
> > > > If Flg = True Then Cells(1, 3) = True
> > > >
> > > > End Select
> > > >
> > > > End Sub
> > >
> > >
>
>

=?Utf-8?B?S3JhZ2VsdW5k?= <(E-Mail Removed)> wrote in
news:2629FAFA-AA30-4F0A-B870-(E-Mail Removed):

> Help pls! I need a procedure which can help me to select a selection A
> and B respectively, so that selection A is chosen every other time the
> procedure runs (and the same for selection B). I intended to make a
> procedure that sets the flag to TRUE when the flag is FALSE and vice
> versa. In the code presented below, the boolean value remains FALSE and
> never changes to TRUE.
>
> What I am doing wrong??
>
> Public Sub Insert_Flag()
> Dim Worksheet As Worksheet
> Dim Flg As Boolean
>
> Set Worksheet = ActiveSheet
>
> Select Case Flg
>
> Case Flg = True (Selection A)
> Flg = False
> If Flg = False Then Cells(1, 3) = False
>
> Case Flg = False (Selection B)
> Flg = True
> If Flg = True Then Cells(1, 3) = True
>
> End Select
>
> End Sub
>
I think your flag needs to be public so that it doesn't get re-initialized
between executions of your subroutine. I doubt that private variables
'exist' when the subroutine isn't active.

I think also that there is no guarantee that Flg will hold any specific
value the first time you run your routine (or it might be null).

You could use the contents of a cell in your workbook as your flag, if you
don't want a global variable. You could start with 1 or -1 and multiply it
by -1 every time your routine executes.




--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

bz+(E-Mail Removed) remove ch100-5 to avoid spam trap

One more way -

Public gbFlag As Boolean

Public Sub Insert_Flag2()
Cells(1, 3) = gbFlag
gbFlag = Not gbFlag
End Sub

Regards,
Peter T

"Kragelund" <(E-Mail Removed)> wrote in message
news:A0C1E435-0022-4AAD-9F58-(E-Mail Removed)...
> John,
>
> Given the state of my own wet matter, I don't question your sanity. Your
> version works fine. My problem here was that I had defined Case Flg =
True,
> which didn't work, whereas you defined it Case True which works perfectly.
As
> you may have guessed I'm a newbie.
>
> Many thanks, you saved me quite a number of my valuable few remaining
hairs.
>
> Henrik
>
>
> "John Coleman" wrote:
>
> > By all means go the static approach if it works for you. It is probably
> > a cleaner approach unless other procedures need access to the same
> > flag. Still - the Public variable approach should work. I just tried
> > the following code to check my sanity (which is often an open
> > question):
> >
> > Public Flg As Boolean
> >
> > Public Sub Insert_Flag()
> >
> > Select Case Flg
> > Case True
> > Flg = False
> > If Flg = False Then Cells(1, 3) = False
> > Case False
> > Flg = True
> > If Flg = True Then Cells(1, 3) = True
> >
> > End Select
> > End Sub
> >
> > It toggles back and forth for me with no problem.
> >
> > Are you sure that you declared the public variable *outside* the
> > procedure? If you did, are you sure that you eliminated the local
> > variable declaration *inside* the procedure as well? Any local
> > declaration would hide any global declaration within the scope of the
> > procedure.
> >
> > -John Coleman
> >
> > Kragelund wrote:
> > > Dear John & Niek,
> > >
> > > thanks a bunch for your input, I had no idea I needed to declare a
static
> > > variable. I did as you suggested, John, but unfortunately, the
procedure
> > > still doesn't toggle anywhere away from FALSE. Could there be any
other
> > > points I should take into consideration? I am running the procedure
from
> > > general code module (not a worksheet one).
> > >
> > > Thanks in advance
> > > Henrik
> > >
> > >
> > > "John Coleman" wrote:
> > >
> > > > You have declared Flg to be a local (private) variable to the
> > > > procedure. You need a public variable. In a general code module,
> > > > outside of any sub or function, have the line
> > > >
> > > > Public Flg as Boolean
> > > >
> > > > and delete the line
> > > >
> > > > Dim Flg as Boolean
> > > >
> > > > from your code. This will toggle back and forth, It will start
false
> > > > when you open Excel or whenever you reset the project. If you want
> > > > persistence across sessions you need to do something like hide the
flag
> > > > in an out-of-the-way cell or something fancier like a registry
setting
> > > > or entry in the names collection.
> > > >
> > > > HTH
> > > >
> > > > -John Coleman
> > > >
> > > > Kragelund wrote:
> > > > > Help pls! I need a procedure which can help me to select a
selection A and B
> > > > > respectively, so that selection A is chosen every other time the
procedure
> > > > > runs (and the same for selection B). I intended to make a
procedure that sets
> > > > > the flag to TRUE when the flag is FALSE and vice versa. In the
code presented
> > > > > below, the boolean value remains FALSE and never changes to TRUE.
> > > > >
> > > > > What I am doing wrong??
> > > > >
> > > > > Public Sub Insert_Flag()
> > > > > Dim Worksheet As Worksheet
> > > > > Dim Flg As Boolean
> > > > >
> > > > > Set Worksheet = ActiveSheet
> > > > >
> > > > > Select Case Flg
> > > > >
> > > > > Case Flg = True (Selection A)
> > > > > Flg = False
> > > > > If Flg = False Then Cells(1, 3) = False
> > > > >
> > > > > Case Flg = False (Selection B)
> > > > > Flg = True
> > > > > If Flg = True Then Cells(1, 3) = True
> > > > >
> > > > > End Select
> > > > >
> > > > > End Sub
> > > >
> > > >
> >
> >

Peter T and bz,

thanks for your input. Both are super elegant solutions. Thanks also for
helping me expand my knowledge of vb and programming logic in general.

Henrik


"bz" wrote:

> =?Utf-8?B?S3JhZ2VsdW5k?= <(E-Mail Removed)> wrote in
> news:2629FAFA-AA30-4F0A-B870-(E-Mail Removed):
>
> > Help pls! I need a procedure which can help me to select a selection A
> > and B respectively, so that selection A is chosen every other time the
> > procedure runs (and the same for selection B). I intended to make a
> > procedure that sets the flag to TRUE when the flag is FALSE and vice
> > versa. In the code presented below, the boolean value remains FALSE and
> > never changes to TRUE.
> >
> > What I am doing wrong??
> >
> > Public Sub Insert_Flag()
> > Dim Worksheet As Worksheet
> > Dim Flg As Boolean
> >
> > Set Worksheet = ActiveSheet
> >
> > Select Case Flg
> >
> > Case Flg = True (Selection A)
> > Flg = False
> > If Flg = False Then Cells(1, 3) = False
> >
> > Case Flg = False (Selection B)
> > Flg = True
> > If Flg = True Then Cells(1, 3) = True
> >
> > End Select
> >
> > End Sub
> >
> I think your flag needs to be public so that it doesn't get re-initialized
> between executions of your subroutine. I doubt that private variables
> 'exist' when the subroutine isn't active.
>
> I think also that there is no guarantee that Flg will hold any specific
> value the first time you run your routine (or it might be null).
>
> You could use the contents of a cell in your workbook as your flag, if you
> don't want a global variable. You could start with 1 or -1 and multiply it
> by -1 every time your routine executes.
>
>
>
>
> --
> bz
>
> please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
> infinite set.
>
> bz+(E-Mail Removed) remove ch100-5 to avoid spam trap
>