I have a listbox on a sheet. In the listbox change procedure I do a test.
If the test is positive I want to suppress the listbox from changing . I
tried to do this by changing the listbox.listindex to the previous value.
However, that triggers the change procedure again. I tried to use a
modification of this code but to no avail.
Private Sub TextBox1_Change()
Static AutoAction As Boolean
Dim TabPos As String
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
With Me.TextBox1
' Your code here.
' For example,
.Text = Replace(.Text, Chr(9), "")tiggering code
End With
AutoAction = False
End Sub
In this code the triggering code does not trigger the second time thru. In
my case it does. I welcome any suggestions.

--
russ

Russ,

It works for me.

I changed the "triggering code" to:

..Text = .Text & "A"

Without your AutoAction check, the textbox fills with "A"s. With it, one
"A" is added for each letter I type in the box, which is what I would
expect.

hth,

Doug

"Russ" wrote in message
...
I have a listbox on a sheet. In the listbox change procedure I do a test.
If the test is positive I want to suppress the listbox from changing . I
tried to do this by changing the listbox.listindex to the previous value.
However, that triggers the change procedure again. I tried to use a
modification of this code but to no avail.
Private Sub TextBox1_Change()
Static AutoAction As Boolean
Dim TabPos As String
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
With Me.TextBox1
' Your code here.
' For example,
.Text = Replace(.Text, Chr(9), "")'tiggering code
End With
AutoAction = False
End Sub
In this code the triggering code does not trigger the second time thru.
In
my case it does. I welcome any suggestions.

--
russ

maybe this would work - you could have a non-visible boolean checkbox
that, when the spreadsheet opens, sets itself to false. when the
change procedure is run, it checks for the boolean value & only runs
if the value is false. @ the end it changes that boolean value to
true.
so the change will only run once each time the workbook is opened.
i've done this kind of thing on userforms & don't know if you can
modify the theory to work with spreadsheet controls, but i think you
could.
OR you could use a value on a hidden worksheet that would act as the
boolean.
susan


On Mar 14, 11:17 pm, "Doug Glancy"
wrote:
Russ,

It works for me.

I changed the "triggering code" to:

.Text = .Text & "A"

Without your AutoAction check, the textbox fills with "A"s. With it, one
"A" is added for each letter I type in the box, which is what I would
expect.

hth,

Doug

"Russ" wrote in message

...



I have a listbox on a sheet. In the listbox change procedure I do a test.
If the test is positive I want to suppress the listbox from changing . I
tried to do this by changing the listbox.listindex to the previous value.
However, that triggers the change procedure again. I tried to use a
modification of this code but to no avail.
Private Sub TextBox1_Change()
Static AutoAction As Boolean
Dim TabPos As String
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
With Me.TextBox1
' Your code here.
' For example,
.Text = Replace(.Text, Chr(9), "")'tiggering code
End With
AutoAction = False
End Sub
In this code the triggering code does not trigger the second time thru.
In
my case it does. I welcome any suggestions.

--
russ- Hide quoted text -

- Show quoted text -

Susan,
I think what you propose works similar to the AutoAction boolean variable in
my example code for the textbox. The routine runs only once surpressing the
change in the textbox. I am trying to surpress a listbox from changing when
clicked on when a test in the change procedure fails. I am working up a
simple example to show the problem.
Thanks for your try.
--
russ


"Susan" wrote:

maybe this would work - you could have a non-visible boolean checkbox
that, when the spreadsheet opens, sets itself to false. when the
change procedure is run, it checks for the boolean value & only runs
if the value is false. @ the end it changes that boolean value to
true.
so the change will only run once each time the workbook is opened.
i've done this kind of thing on userforms & don't know if you can
modify the theory to work with spreadsheet controls, but i think you
could.
OR you could use a value on a hidden worksheet that would act as the
boolean.
susan


On Mar 14, 11:17 pm, "Doug Glancy"
wrote:
Russ,

It works for me.

I changed the "triggering code" to:

.Text = .Text & "A"

Without your AutoAction check, the textbox fills with "A"s. With it, one
"A" is added for each letter I type in the box, which is what I would
expect.

hth,

Doug

"Russ" wrote in message

...



I have a listbox on a sheet. In the listbox change procedure I do a test.
If the test is positive I want to suppress the listbox from changing . I
tried to do this by changing the listbox.listindex to the previous value.
However, that triggers the change procedure again. I tried to use a
modification of this code but to no avail.
Private Sub TextBox1_Change()
Static AutoAction As Boolean
Dim TabPos As String
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
With Me.TextBox1
' Your code here.
' For example,
.Text = Replace(.Text, Chr(9), "")'tiggering code
End With
AutoAction = False
End Sub
In this code the triggering code does not trigger the second time thru.
In
my case it does. I welcome any suggestions.

--
russ- Hide quoted text -

- Show quoted text -

Susan,
Following up on my last reply, I built a simple listbox on a sheet filled
with the numbers 1 to 10. The change procedure is the following:
Private Sub ListBox1_Change()
If Cells(17, 3) < Cells(15, 3) Then 'surpress
Cells(16, 2) = Me.ListBox1.ListIndex
Me.ListBox1.ListIndex = Cells(15, 2)
Else 'let the listbox change
Cells(15, 2) = Me.ListBox1.Value
Cells(16, 2) = Me.ListBox1.ListIndex
End If
End Sub

If cells(17, 3) are not equal to cells(15, 3) then cells(15, 2) and
cells(16, 2) do not change however the listbox still changes when clicked on.
I would like the listbox not to change when the test fails.

--
russ


"Susan" wrote:

maybe this would work - you could have a non-visible boolean checkbox
that, when the spreadsheet opens, sets itself to false. when the
change procedure is run, it checks for the boolean value & only runs
if the value is false. @ the end it changes that boolean value to
true.
so the change will only run once each time the workbook is opened.
i've done this kind of thing on userforms & don't know if you can
modify the theory to work with spreadsheet controls, but i think you
could.
OR you could use a value on a hidden worksheet that would act as the
boolean.
susan


On Mar 14, 11:17 pm, "Doug Glancy"
wrote:
Russ,

It works for me.

I changed the "triggering code" to:

.Text = .Text & "A"

Without your AutoAction check, the textbox fills with "A"s. With it, one
"A" is added for each letter I type in the box, which is what I would
expect.

hth,

Doug

"Russ" wrote in message

...



I have a listbox on a sheet. In the listbox change procedure I do a test.
If the test is positive I want to suppress the listbox from changing . I
tried to do this by changing the listbox.listindex to the previous value.
However, that triggers the change procedure again. I tried to use a
modification of this code but to no avail.
Private Sub TextBox1_Change()
Static AutoAction As Boolean
Dim TabPos As String
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
With Me.TextBox1
' Your code here.
' For example,
.Text = Replace(.Text, Chr(9), "")'tiggering code
End With
AutoAction = False
End Sub
In this code the triggering code does not trigger the second time thru.
In
my case it does. I welcome any suggestions.

--
russ- Hide quoted text -

- Show quoted text -

Doug,
The code I posted works fine as is. The problem I have is when I try to
adopt the code to surpress a listbox from changing I can not make it work.
Replace the TextBox1_Change with ListBox1_Change and see if you can construct
code that does a simple test that stops the listbox from changing when you
click on an item.
--
russ


"Doug Glancy" wrote:

Russ,

It works for me.

I changed the "triggering code" to:

..Text = .Text & "A"

Without your AutoAction check, the textbox fills with "A"s. With it, one
"A" is added for each letter I type in the box, which is what I would
expect.

hth,

Doug

"Russ" wrote in message
...
I have a listbox on a sheet. In the listbox change procedure I do a test.
If the test is positive I want to suppress the listbox from changing . I
tried to do this by changing the listbox.listindex to the previous value.
However, that triggers the change procedure again. I tried to use a
modification of this code but to no avail.
Private Sub TextBox1_Change()
Static AutoAction As Boolean
Dim TabPos As String
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
With Me.TextBox1
' Your code here.
' For example,
.Text = Replace(.Text, Chr(9), "")'tiggering code
End With
AutoAction = False
End Sub
In this code the triggering code does not trigger the second time thru.
In
my case it does. I welcome any suggestions.

--
russ

Doug.
I built a simple listbox on a sheet filled with the numbers 1 to 10. The
change procedure is the following:
Private Sub ListBox1_Change()
If Cells(17, 3) < Cells(15, 3) Then 'surpress
Cells(16, 2) = Me.ListBox1.ListIndex
Me.ListBox1.ListIndex = Cells(15, 2)
Else 'let the listbox change
Cells(15, 2) = Me.ListBox1.Value
Cells(16, 2) = Me.ListBox1.ListIndex
End If
End Sub

If cells(17, 3) are not equal to cells(15, 3) then cells(15, 2) and
cells(16, 2) do not change however the listbox still changes when clicked on.
I would like the listbox to not change.

--
russ


"Doug Glancy" wrote:

Russ,

It works for me.

I changed the "triggering code" to:

..Text = .Text & "A"

Without your AutoAction check, the textbox fills with "A"s. With it, one
"A" is added for each letter I type in the box, which is what I would
expect.

hth,

Doug

"Russ" wrote in message
...
I have a listbox on a sheet. In the listbox change procedure I do a test.
If the test is positive I want to suppress the listbox from changing . I
tried to do this by changing the listbox.listindex to the previous value.
However, that triggers the change procedure again. I tried to use a
modification of this code but to no avail.
Private Sub TextBox1_Change()
Static AutoAction As Boolean
Dim TabPos As String
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
With Me.TextBox1
' Your code here.
' For example,
.Text = Replace(.Text, Chr(9), "")'tiggering code
End With
AutoAction = False
End Sub
In this code the triggering code does not trigger the second time thru.
In
my case it does. I welcome any suggestions.

--
russ

Russ,

I see now what you were asking for. I actually don't have time to test
this, but I below is my attempt to merge your original example and your
listbox code. I think the supress only applies to the part of the evaluates
to true, so that's where I put the "AutoAction" code. In your most recent
example, you said "let the listbox change" under the Else statement, but
there I don't see why it would. There is no change in that part of the code
that would put it into an endless loop of changes. I'm no doubt missing
something again, but here's my best shot:

Private Sub ListBox1_Change()
Static AutoAction As Boolean
If Cells(17, 3) < Cells(15, 3) Then 'surpress
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
Cells(16, 2) = Me.ListBox1.ListIndex
Me.ListBox1.ListIndex = Cells(15, 2)
AutoAction = False
Else 'let the listbox change
Cells(15, 2) = Me.ListBox1.Value
Cells(16, 2) = Me.ListBox1.ListIndex
End If
End Sub

hth,

Doug

"Russ" wrote in message
...
Doug.
I built a simple listbox on a sheet filled with the numbers 1 to 10. The
change procedure is the following:
Private Sub ListBox1_Change()
If Cells(17, 3) < Cells(15, 3) Then 'surpress
Cells(16, 2) = Me.ListBox1.ListIndex
Me.ListBox1.ListIndex = Cells(15, 2)
Else 'let the listbox change
Cells(15, 2) = Me.ListBox1.Value
Cells(16, 2) = Me.ListBox1.ListIndex
End If
End Sub

If cells(17, 3) are not equal to cells(15, 3) then cells(15, 2) and
cells(16, 2) do not change however the listbox still changes when clicked
on.
I would like the listbox to not change.

--
russ


"Doug Glancy" wrote:

Russ,

It works for me.

I changed the "triggering code" to:

..Text = .Text & "A"

Without your AutoAction check, the textbox fills with "A"s. With it, one
"A" is added for each letter I type in the box, which is what I would
expect.

hth,

Doug

"Russ" wrote in message
...
I have a listbox on a sheet. In the listbox change procedure I do a
test.
If the test is positive I want to suppress the listbox from changing .
I
tried to do this by changing the listbox.listindex to the previous
value.
However, that triggers the change procedure again. I tried to use a
modification of this code but to no avail.
Private Sub TextBox1_Change()
Static AutoAction As Boolean
Dim TabPos As String
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
With Me.TextBox1
' Your code here.
' For example,
.Text = Replace(.Text, Chr(9), "")'tiggering code
End With
AutoAction = False
End Sub
In this code the triggering code does not trigger the second time thru.
In
my case it does. I welcome any suggestions.

--
russ

Doug,
Interesting difference between your code and mine:

My code:
Test true (cells unequal)
Both cell(15,2) listbox1.value and cell(16,3) listbox1.listindex do not
change. Listbox1 continues to change when clicked.
Test false (cells equal)
Both cell(15,2) and cell(16,3) change and obviously the listbox1 changes
when clicked.

Your code:
Test true (cells unequal)
cell(15,2) listbox1.value does not change but cell(16,3) listbox1.listindex
does change. Listbox1 continues to change when clicked
Test false(cells equal)
Both cell(15,2) and cell(16,3) change and obviously listbox1 changes when
clicked.

So while different listbox1 continues to change. What I am trying to do is
to get listbox1 not to change when clicked when the test is true.

--
russ


"Doug Glancy" wrote:

Russ,

I see now what you were asking for. I actually don't have time to test
this, but I below is my attempt to merge your original example and your
listbox code. I think the supress only applies to the part of the evaluates
to true, so that's where I put the "AutoAction" code. In your most recent
example, you said "let the listbox change" under the Else statement, but
there I don't see why it would. There is no change in that part of the code
that would put it into an endless loop of changes. I'm no doubt missing
something again, but here's my best shot:

Private Sub ListBox1_Change()
Static AutoAction As Boolean
If Cells(17, 3) < Cells(15, 3) Then 'surpress
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
Cells(16, 2) = Me.ListBox1.ListIndex
Me.ListBox1.ListIndex = Cells(15, 2)
AutoAction = False
Else 'let the listbox change
Cells(15, 2) = Me.ListBox1.Value
Cells(16, 2) = Me.ListBox1.ListIndex
End If
End Sub

hth,

Doug

"Russ" wrote in message
...
Doug.
I built a simple listbox on a sheet filled with the numbers 1 to 10. The
change procedure is the following:
Private Sub ListBox1_Change()
If Cells(17, 3) < Cells(15, 3) Then 'surpress
Cells(16, 2) = Me.ListBox1.ListIndex
Me.ListBox1.ListIndex = Cells(15, 2)
Else 'let the listbox change
Cells(15, 2) = Me.ListBox1.Value
Cells(16, 2) = Me.ListBox1.ListIndex
End If
End Sub

If cells(17, 3) are not equal to cells(15, 3) then cells(15, 2) and
cells(16, 2) do not change however the listbox still changes when clicked
on.
I would like the listbox to not change.

--
russ


"Doug Glancy" wrote:

Russ,

It works for me.

I changed the "triggering code" to:

..Text = .Text & "A"

Without your AutoAction check, the textbox fills with "A"s. With it, one
"A" is added for each letter I type in the box, which is what I would
expect.

hth,

Doug

"Russ" wrote in message
...
I have a listbox on a sheet. In the listbox change procedure I do a
test.
If the test is positive I want to suppress the listbox from changing .
I
tried to do this by changing the listbox.listindex to the previous
value.
However, that triggers the change procedure again. I tried to use a
modification of this code but to no avail.
Private Sub TextBox1_Change()
Static AutoAction As Boolean
Dim TabPos As String
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
With Me.TextBox1
' Your code here.
' For example,
.Text = Replace(.Text, Chr(9), "")'tiggering code
End With
AutoAction = False
End Sub
In this code the triggering code does not trigger the second time thru.
In
my case it does. I welcome any suggestions.

--
russ

Russ,

Are you saying that in certain situations you want people to click on a
different choice listbox but not have the listbox change at all? If so, I'm
not sure how to accomplish that. (I thought you were just trying to prevent
a listbox change from creating an endless loop of further changes).

If you're trying to just keep them from changing a value in a listbox in
certain situations, maybe you should disable the listbox in those
situations? Or remove the choice that you don't want them to make from the
listbox? I'm not sure, but I think maybe you should start a new thread just
stating what you want, and maybe without the example.

Sorry I can't be more help,

Doug

"Russ" wrote in message
...
Doug,
Interesting difference between your code and mine:

My code:
Test true (cells unequal)
Both cell(15,2) listbox1.value and cell(16,3) listbox1.listindex do not
change. Listbox1 continues to change when clicked.
Test false (cells equal)
Both cell(15,2) and cell(16,3) change and obviously the listbox1 changes
when clicked.

Your code:
Test true (cells unequal)
cell(15,2) listbox1.value does not change but cell(16,3)
listbox1.listindex
does change. Listbox1 continues to change when clicked
Test false(cells equal)
Both cell(15,2) and cell(16,3) change and obviously listbox1 changes when
clicked.

So while different listbox1 continues to change. What I am trying to do
is
to get listbox1 not to change when clicked when the test is true.

--
russ


"Doug Glancy" wrote:

Russ,

I see now what you were asking for. I actually don't have time to test
this, but I below is my attempt to merge your original example and your
listbox code. I think the supress only applies to the part of the
evaluates
to true, so that's where I put the "AutoAction" code. In your most
recent
example, you said "let the listbox change" under the Else statement, but
there I don't see why it would. There is no change in that part of the
code
that would put it into an endless loop of changes. I'm no doubt missing
something again, but here's my best shot:

Private Sub ListBox1_Change()
Static AutoAction As Boolean
If Cells(17, 3) < Cells(15, 3) Then 'surpress
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
Cells(16, 2) = Me.ListBox1.ListIndex
Me.ListBox1.ListIndex = Cells(15, 2)
AutoAction = False
Else 'let the listbox change
Cells(15, 2) = Me.ListBox1.Value
Cells(16, 2) = Me.ListBox1.ListIndex
End If
End Sub

hth,

Doug

"Russ" wrote in message
...
Doug.
I built a simple listbox on a sheet filled with the numbers 1 to 10.
The
change procedure is the following:
Private Sub ListBox1_Change()
If Cells(17, 3) < Cells(15, 3) Then 'surpress
Cells(16, 2) = Me.ListBox1.ListIndex
Me.ListBox1.ListIndex = Cells(15, 2)
Else 'let the listbox change
Cells(15, 2) = Me.ListBox1.Value
Cells(16, 2) = Me.ListBox1.ListIndex
End If
End Sub

If cells(17, 3) are not equal to cells(15, 3) then cells(15, 2) and
cells(16, 2) do not change however the listbox still changes when
clicked
on.
I would like the listbox to not change.

--
russ


"Doug Glancy" wrote:

Russ,

It works for me.

I changed the "triggering code" to:

..Text = .Text & "A"

Without your AutoAction check, the textbox fills with "A"s. With it,
one
"A" is added for each letter I type in the box, which is what I would
expect.

hth,

Doug

"Russ" wrote in message
...
I have a listbox on a sheet. In the listbox change procedure I do a
test.
If the test is positive I want to suppress the listbox from changing
.
I
tried to do this by changing the listbox.listindex to the previous
value.
However, that triggers the change procedure again. I tried to use
a
modification of this code but to no avail.
Private Sub TextBox1_Change()
Static AutoAction As Boolean
Dim TabPos As String
If AutoAction = True Then
Exit Sub
End If
AutoAction = True
With Me.TextBox1
' Your code here.
' For example,
.Text = Replace(.Text, Chr(9), "")'tiggering code
End With
AutoAction = False
End Sub
In this code the triggering code does not trigger the second time
thru.
In
my case it does. I welcome any suggestions.

--
russ