Macro to Call A Subroutine on a Sheet Sometimes stops?

R

RJQMAN

I have an odd frustrating problem. I have a very large program that I
just completed (I thought). When testing though, I unexpecedly found
that it sometimes hangs up, and I cannot figure out why. Here is what
occurs...

I have a macro on one worksheet (worksheet 12 - 'Menu') driven by a
button. When it is triggered, it calls another macro that is on a
different worksheet (worksheet 14 - 'Contest Data'. It seems to work
just fine - most of the time.

Once in a while - perhaps 5% - 10% of the time - the macro just stops,
and I get an error message.

I use the F8 key starting with the highlighted line displayed by the
error message (which is the call line), and it runs fine going step-by-
step. All I need to do is step past the 'call' line, and then I can
click on continue, and it runs, and I don't need to do anything at all
to step by the 'call' line except to use the F8 key. I can't seem to
make it hang up at all going through it with the F8 key to debug. I
can run this section 20 or 30 times, and then it will suddenly hang
up, even though I have made no changes on any sheet.

Here is the snippet of code involved...The sheet I am calling is
called 'Contest Data' and it is Sheet 14.

The button triggers the following macro (which is on sheet 12)...

Sub FMBC01_D01_GoTo_Contest_Data()

' First the subroutine makes the new sheet visible and hides other
sheets (I am skipping a couple of lines of the program that hide other
sheets and hide the tabs) - then

This Workbook.Sheets("Contest Data").Visible=True
This Workbook.Sheets("Menu").Visible=False
ThisWorkbook.Sheets("Contest Data").Activate
ThisWorkbook.Sheets("Contest Data").Unprotect

Zoom = 100

Call Sheet14.SetUpContestDataAudit
'
End Sub

Then on the new sheet, which is called 'Contest Data', the sub is

Sub SetUpContestDataAudit()

Columns("A:E").Hidden = False
Columns("F:R").Hidden = True

' The sub then continues to hide some rows and columns and display
others.

The sub runs fine once I get past the call statement on sheet 12. It
seems to hang up on sheet 12 - about once out of 20 or 25 times - and
then it runs fine just by my pressing F8. What am I doing wrong???
Did I write the call statement incorrectly?

Any help would be appreciated - this group has been my only external
teacher, so I am pretty much self-taught on Excel and VBA in general,
and that means my teacher did not know any more than I did...

Thanks...
 
R

RJQMAN

I have an odd frustrating problem.  I have a very large program that I
just completed (I thought).  When testing though, I unexpecedly found
that it sometimes hangs up, and I cannot figure out why.  Here is what
occurs...

I have a macro on one worksheet (worksheet 12 - 'Menu') driven by a
button.  When it is triggered, it calls another macro that is on a
different worksheet (worksheet 14 - 'Contest Data'.  It seems to work
just fine - most of the time.

Once in a while - perhaps 5% - 10% of the time - the macro just stops,
and I get an error message.

I use the F8 key starting with the highlighted line displayed by the
error message (which is the call line), and it runs fine going step-by-
step.  All I need to do is step past the 'call' line, and then I can
click on continue, and it runs, and I don't need to do anything at all
to step by the 'call' line except to use the F8 key.  I can't seem to
make it hang up at all going through it with the F8 key to debug.  I
can run this section 20 or 30 times, and then it will suddenly hang
up, even though I have made no changes on any sheet.

Here is the snippet of code involved...The sheet I am calling is
called 'Contest Data' and it is Sheet 14.

The button triggers the following macro (which is on sheet 12)...

Sub FMBC01_D01_GoTo_Contest_Data()

' First the subroutine makes the new sheet visible and hides other
sheets (I am skipping a couple of lines of the program that hide other
sheets and hide the tabs) - then

    This Workbook.Sheets("Contest Data").Visible=True
    This Workbook.Sheets("Menu").Visible=False
    ThisWorkbook.Sheets("Contest Data").Activate
    ThisWorkbook.Sheets("Contest Data").Unprotect

    Zoom = 100

    Call Sheet14.SetUpContestDataAudit
'
End Sub

Then on the new sheet, which is called 'Contest Data', the sub is

Sub SetUpContestDataAudit()

    Columns("A:E").Hidden = False
    Columns("F:R").Hidden = True

' The sub then continues to hide some rows and columns and display
others.

The sub runs fine once I get past the call statement on sheet 12.  It
seems to hang up on sheet 12 - about once out of 20 or 25 times - and
then it runs fine just by my pressing F8.  What am I doing wrong???
Did I write the call statement incorrectly?

Any help would be appreciated - this group has been my only external
teacher, so I am pretty much self-taught on Excel and VBA in general,
and that means my teacher did not know any more than I did...

Thanks...

I really need help. I tried using a different type of button on sheet
12 - the sheet that has the macro to call the second sheet. I had
been using an ActiveX (I think that is what it is called) - it was one
of those where you could specify the color of the button. I went to
one of the more conventional buttons. But once again the program hung
up on about the 12th try. I neglected to get the error number,
unfortunately. I have been trying for most of the day to get it to
lock up again, but it has not done so. I am afraid if I send the
program out, some 'soccer mom' user will have the program fail.

Isn't there some way that I can tell the program to ignore an error on
that statement? Since everything runs fine except for the program
stopping and flashing the darn error message, that would be a possible
work-around if no one can help me and if there is no weakness inherent
in the way I wrote the code. This must be simple for some of you
experts, but it is baffling to me.

I could really use some help. Thanks in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top