Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 54
Default Macro to Call A Subroutine on a Sheet Sometimes stops?

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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 54
Default Macro to Call A Subroutine on a Sheet Sometimes stops?

On Jul 10, 4:05*pm, RJQMAN wrote:
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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Call a subroutine from ThisWorkBook Dave Peterson[_2_] Excel Programming 0 May 17th 10 01:13 PM
Call subroutine problem rick Excel Programming 1 November 13th 09 10:01 AM
Call Subroutine in Excel DogLover Excel Programming 3 November 5th 09 10:31 AM
Call a subroutine using variable subroutine name dhstein Excel Discussion (Misc queries) 3 July 26th 09 08:28 PM
Can't call a subroutine donwb Excel Programming 3 August 6th 08 05:05 AM


All times are GMT +1. The time now is 03:54 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"