Weird Macro Problem

[Bruce001]

Hello. I am new here. I have run into a problem with a macro and I don't have much hair left to pull out. I have a macro that resets default values to cells on several sheets of my workbook. This works fine except for a few values and I can't see a pattern other than the only fields that I am having a problem with are ones I'm trying to set to "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 debug on this subroutine and at each of these three lines, it would jump to that sheets macros and execute the code found first??? (Strangely enough, this code found should set the values to what I want, but it doesn't seem to set them.)

I have run this under Excel 2003 and Excel 2000 and the results are the 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).

--
Regards,
Tom Ogilvy

"Bruce001" <[email protected]> wrote in
message news:[email protected]...
>
> Hello. I am new here. I have run into a problem with a macro and I don't
> have much hair left to pull out. I have a macro that resets default
> values to cells on several sheets of my workbook. This works fine
> except for a few values and I can't see a pattern other than the only
> fields that I am having a problem with are ones I'm trying to set to
> "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 debug
> on this subroutine and at each of these three lines, it would jump to
> that sheets macros and execute the code found first??? (Strangely
> enough, this code found should set the values to what I want, but it
> doesn't seem to set them.)
>
> I have run this under Excel 2003 and Excel 2000 and the results are the
> same. Any help would be appreciated.
>
>
> --
> Bruce001
> ------------------------------------------------------------------------
> Bruce001's Profile:
http://www.excelforum.com/member.php...o&userid=26630
> View this thread: http://www.excelforum.com/showthread...hreadid=399045
>

[Bruce001]

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

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

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

Bruce Gold

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

--
Regards,
Tom Ogilvy



"Bruce001" <[email protected]> wrote in
message news:[email protected]...
>
> THanks for the reply Tom. I tried a separate subroutine like you
> suggested and it does the same thing. I have no idea why Excel would go
> to the individual sheets macros like it does.
>
> Let me give a bit more information. I use radio buttons on the page to
> set these cells to true or false. I did notice that some of my buttons
> had the same names across the different sheets, so I went back and
> changed them to all unique. And it still does the same thing. What is
> truely weird is I have a bunch of other sheets in this workbook that do
> the same thing and those sheets don't act that way.
>
> Any other help would be appreciated since I am running out of ideas and
> time.
>
> Bruce Gold
>
>
> --
> Bruce001
> ------------------------------------------------------------------------
> Bruce001's Profile:
http://www.excelforum.com/member.php...o&userid=26630
> View this thread: http://www.excelforum.com/showthread...hreadid=399045
>

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

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

--
Regards,
Tom Ogilvy



"Bruce001" <[email protected]> wrote in
message news:[email protected]...
>
> THanks for the reply Tom. I tried a separate subroutine like you
> suggested and it does the same thing. I have no idea why Excel would go
> to the individual sheets macros like it does.
>
> Let me give a bit more information. I use radio buttons on the page to
> set these cells to true or false. I did notice that some of my buttons
> had the same names across the different sheets, so I went back and
> changed them to all unique. And it still does the same thing. What is
> truely weird is I have a bunch of other sheets in this workbook that do
> the same thing and those sheets don't act that way.
>
> Any other help would be appreciated since I am running out of ideas and
> time.
>
> Bruce Gold
>
>
> --
> Bruce001
> ------------------------------------------------------------------------
> Bruce001's Profile:
http://www.excelforum.com/member.php...o&userid=26630
> View this thread: http://www.excelforum.com/showthread...hreadid=399045
>

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

--
Regards,
Tom Ogilvy




"Bruce001" <[email protected]> wrote in
message news:[email protected]...
>
> THanks for the reply Tom. I tried a separate subroutine like you
> suggested and it does the same thing. I have no idea why Excel would go
> to the individual sheets macros like it does.
>
> Let me give a bit more information. I use radio buttons on the page to
> set these cells to true or false. I did notice that some of my buttons
> had the same names across the different sheets, so I went back and
> changed them to all unique. And it still does the same thing. What is
> truely weird is I have a bunch of other sheets in this workbook that do
> the same thing and those sheets don't act that way.
>
> Any other help would be appreciated since I am running out of ideas and
> time.
>
> Bruce Gold
>
>
> --
> Bruce001
> ------------------------------------------------------------------------
> Bruce001's Profile:
http://www.excelforum.com/member.php...o&userid=26630
> View this thread: http://www.excelforum.com/showthread...hreadid=399045
>

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

--
Regards,
Tom Ogilvy


"Bruce001" <[email protected]> wrote in
message news:[email protected]...
>
> Tom Ogilvy Wrote:
> > 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.
>
> Tom Ogilvy Wrote:
> > 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.
>
> 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 Wrote:
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Bruce001" <[email protected]>
> > wrote in
> > message news:[email protected]...
> > >
> > > THanks for the reply Tom. I tried a separate subroutine like you
> > > suggested and it does the same thing. I have no idea why Excel would
> > go
> > > to the individual sheets macros like it does.
> > >
> > > Let me give a bit more information. I use radio buttons on the page
> > to
> > > set these cells to true or false. I did notice that some of my
> > buttons
> > > had the same names across the different sheets, so I went back and
> > > changed them to all unique. And it still does the same thing. What
> > is
> > > truely weird is I have a bunch of other sheets in this workbook that
> > do
> > > the same thing and those sheets don't act that way.
> > >
> > > Any other help would be appreciated since I am running out of ideas
> > and
> > > time.
> > >
> > > Bruce Gold
> > >
> > >
> > > --
> > > Bruce001
> > >
> > ------------------------------------------------------------------------
> > > Bruce001's Profile:
> > http://www.excelforum.com/member.php...o&userid=26630
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=399045
> > >
>
>
> --
> Bruce001
> ------------------------------------------------------------------------
> Bruce001's Profile:
http://www.excelforum.com/member.php...o&userid=26630
> View this thread: http://www.excelforum.com/showthread...hreadid=399045
>