Weird Macro Problem

[Bruce001]

Hello. I am new here. I have run into a problem with a macro and I don'
have much hair left to pull out. I have a macro that resets defaul
values to cells on several sheets of my workbook. This works fin
except for a few values and I can't see a pattern other than the onl
fields that I am having a problem with are ones I'm trying to set t
"TRUE". The code looks like this:

Worksheets("RBRQ1").Range("B8:B9").ClearContents
Worksheets("RBRQ1").Range("B12").ClearContents
Worksheets("RBRQ1").Range("B10").Value = 1
Worksheets("RBRQ2").Range("D34").Value = "TRUE"
Worksheets("RBRQ3").Range("D34").Value = "FALSE"
Worksheets("RBRQ4").Range("B8:B10").ClearContents
Worksheets("RBRQ4").Range("D34").Value = "FALSE"
Worksheets("RBRQ5").Range("I9:I10").ClearContents
Worksheets("RBRQ5").Range("D34").Value = "TRUE"
Worksheets("RBRQ6").Range("D34").Value = "TRUE"
Worksheets("RBRQ6").Range("D35").Value = "FALSE"
Worksheets("RBRQ6").Range("D36").Value = "FALSE"
Worksheets("RBRQ6").Range("E34").Value = "FALSE"
Worksheets("RBRQ7").Range("D34").Value = "TRUE"
Worksheets("RBRQ7").Range("D35").Value = "FALSE"
Worksheets("RBRQ7").Range("D36").Value = "FALSE"
Worksheets("RBRQ7").Range("D37").Value = "FALSE"
Worksheets("RBRQ7").Range("E34").Value = "FALSE"

The lines in red are the only ones that don't get set. I ran the debu
on this subroutine and at each of these three lines, it would jump t
that sheets macros and execute the code found first??? (Strangel
enough, this code found should set the values to what I want, but i
doesn't seem to set them.)

I have run this under Excel 2003 and Excel 2000 and the results are th
same. Any help would be appreciated

 

[Tom Ogilvy]

Try running this as a separate macro:

Sub SetToTrue()
Worksheets("RBRQ2").Range("D34").Value = "TRUE"
Worksheets("RBRQ5").Range("D34").Value = "TRUE"
Worksheets("RBRQ6").Range("D34").Value = "TRUE"
Worksheets("RBRQ7").Range("D34").Value = "TRUE"
End Sub

If it doesn't work, then look at the cells and see if they are merged or
there is otherwise a problem with their formatting (conditional formatting
perhaps).

 

[Bruce001]

THanks for the reply Tom. I tried a separate subroutine like yo
suggested and it does the same thing. I have no idea why Excel would g
to the individual sheets macros like it does.

Let me give a bit more information. I use radio buttons on the page t
set these cells to true or false. I did notice that some of my button
had the same names across the different sheets, so I went back an
changed them to all unique. And it still does the same thing. What i
truely weird is I have a bunch of other sheets in this workbook that d
the same thing and those sheets don't act that way.

Any other help would be appreciated since I am running out of ideas an
time.

Bruce Gol

 

[Tom Ogilvy]

Radio buttons only allow one button in the group to be true.

It isunclear what you mean by

I have no idea why Excel would go
to the individual sheets macros like it does.

but this sounds like you are telling me that some event macros are being
fired when you are making the updates. If this is so, then this is the
likely cause of your problem. It sounds like you might have some type of
recursive call going on or other code is altering these cells as well.

 

[Bruce001]

Radio buttons only allow one button in the group to be true.

Yes, this is how I'm using them. And in the macro sheet, I execute code
when a button is selected.

It isunclear what you mean by

but this sounds like you are telling me that some event macros are
being
fired when you are making the updates. If this is so, then this is
the
likely cause of your problem. It sounds like you might have some type
of
recursive call going on or other code is altering these cells as well.

Yes. The event programming looks something like this:

Private Sub AppButton1_Click()
Worksheets("RBRQ7").Range("D34").Value = "FALSE"
Worksheets("RBRQ7").Range("E10").ClearContents
End Sub

How can I get rid of these recursive calls? (And thanks for your
help!)

 

[Tom Ogilvy]

Radio buttons only allow one button in the group to be true.

It isunclear what you mean by

I have no idea why Excel would go
to the individual sheets macros like it does.

but this sounds like you are telling me that some event macros are being
fired when you are making the updates. If this is so, then this is the
likely cause of your problem. It sounds like you might have some type of
recursive call going on or other code is altering these cells as well.

 

[Tom Ogilvy]

In a General Module, declare a Public variable like

Public bBlockEvents as Boolean

then in your macro to update the sheet put in a statement at the top like

bBlockEvents = True

' code to update the sheet

bBlockEvents = False

then in you events add a line like

Private Sub AppButton1_Click()
if bBlockEvents = True then exit sub
Worksheets("RBRQ7").Range("D34").Value = "FALSE"
Worksheets("RBRQ7").Range("E10").ClearContents
End Sub

That is something you can try.