VBA Question: Called macro stops unexpectedly

Hello,

I'm new to using VBA in Excel.  I'm hoping I'm missing something
simple and that someone here can help me.

I have one Excel workbook that has many subroutines/macros in Module
1.  I have 5 worksheets in the workbook and each has navigation
buttons to move from sheet to sheet.  On one sheet, there are required
fields that must be checked before a user can be moved to the desired
sheet.  The buttons call the appropriate macro, like GoTo_Report,
which takes the user to the Report sheet.  But, on the Project Info
screen, I need all these buttons to also run the macro that checks
against null values in the required fields.  I have that code in
ProjInfoReqFields.

ProjInfoReqFields has 8 IF statements.  I want each of these 5 buttons
to call ProjInfoReqFields, have it run through all 8 IF statements,
then call the second macro to go to the appropriate sheet.  If I copy
this code in to each of the 5 navigation buttons, it works great.  It
goes through all 8 IF statements, then I have the final Else statement
calling the second macro.  But, when I try to just call both macros,
as shown below, it will only get through the first IF statement, then
it continues to the second macro.

Button2_Click()

'Macro to check against null values on Project Info sheet
ProjInfoReqFields

'Macro to move user from active sheet to Report sheet
GoTo_Report

End Sub


I made the following modification to the macro, which made it
successfully run through all 8 IF statements, but either wouldn't call
the second macro or something else is awry because it ends up going to
the wrong sheet.

Button2_Click()

'Macro to check against null values on Project Info sheet
ProjInfoReqFields
Exit Sub

'Macro to move user from active sheet to Report sheet
GoTo_Report

End Sub

I appreciate any help.
Thanks!
Tina
0
dmbluv (2)
8/16/2008 5:29:15 PM
excel 39879 articles. 2 followers. Follow

3 Replies
394 Views

Similar Articles

[PageSpeed] 37

Might help if you post all of your code for comments

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"dmbluv" <dmbluv@comcast.net> wrote in message 
news:fbabc9b5-8ca9-4082-a8a9-9419f7f5c521@y21g2000hsf.googlegroups.com...
> Hello,
>
> I'm new to using VBA in Excel.  I'm hoping I'm missing something
> simple and that someone here can help me.
>
> I have one Excel workbook that has many subroutines/macros in Module
> 1.  I have 5 worksheets in the workbook and each has navigation
> buttons to move from sheet to sheet.  On one sheet, there are required
> fields that must be checked before a user can be moved to the desired
> sheet.  The buttons call the appropriate macro, like GoTo_Report,
> which takes the user to the Report sheet.  But, on the Project Info
> screen, I need all these buttons to also run the macro that checks
> against null values in the required fields.  I have that code in
> ProjInfoReqFields.
>
> ProjInfoReqFields has 8 IF statements.  I want each of these 5 buttons
> to call ProjInfoReqFields, have it run through all 8 IF statements,
> then call the second macro to go to the appropriate sheet.  If I copy
> this code in to each of the 5 navigation buttons, it works great.  It
> goes through all 8 IF statements, then I have the final Else statement
> calling the second macro.  But, when I try to just call both macros,
> as shown below, it will only get through the first IF statement, then
> it continues to the second macro.
>
> Button2_Click()
>
> 'Macro to check against null values on Project Info sheet
> ProjInfoReqFields
>
> 'Macro to move user from active sheet to Report sheet
> GoTo_Report
>
> End Sub
>
>
> I made the following modification to the macro, which made it
> successfully run through all 8 IF statements, but either wouldn't call
> the second macro or something else is awry because it ends up going to
> the wrong sheet.
>
> Button2_Click()
>
> 'Macro to check against null values on Project Info sheet
> ProjInfoReqFields
> Exit Sub
>
> 'Macro to move user from active sheet to Report sheet
> GoTo_Report
>
> End Sub
>
> I appreciate any help.
> Thanks!
> Tina 

0
dguillett1 (2487)
8/16/2008 8:59:21 PM
Don,

Unfortunately, my code is on a different system and I can't copy it
here.  But, to give you an idea, I've typed up a quick example below:

Sub ProjInfoReqFields()

If Range("D9") =3D "" Then
  Msgbox "This field is required."
  Range("D9").Select
Else
  If Range("D11") =3D "" Then
    Msgbox "This field is required."
    Range("D11").Select
  Else
    If (so on for another 6 cells)

For purposes of full-disclosure, or as much as I can, here is the code
I have to move the user to the Report sheet, which is called as the
second macro for Button2_Click:

Sub GoTo_Report()
'Moves user to Report screen
Sheets("Report").Select
Range("A1").Select
End Sub

When I call ProjInfoReqFields from another macro, such as
Button2_Click, it will display the message box for D9, and probably
selects D9 as well (too quick for me to tell), but then immediately
moves on to the second macro.  See below:

Button2_Click()
'Macro to check against null values on Project Info sheet
ProjInfoReqFields
'Macro to move user from active sheet to Report sheet
GoTo_Report
End Sub

If I modify it to add Exit Sub after calling ProjInfoReqFields, it
does in fact cycle through all the IF statements, but then does not
run the second macro to take user to desired sheet; it actually takes
user to a sheet that is not called in any macro.  (That's why I think
it's exiting the subroutine before running the second macro.)  See
below:

Button2_Click()
'Macro to check against null values on Project Info sheet
ProjInfoReqFields
Exit Sub
'Macro to move user from active sheet to Report sheet
GoTo_Report
End Sub

Thanks for any guidance!
Tina

On Aug 16, 4:59=A0pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> Might help if you post all of your code for comments
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"dmbluv" <dmb...@comcast.net> wrote in message
>
> news:fbabc9b5-8ca9-4082-a8a9-9419f7f5c521@y21g2000hsf.googlegroups.com...
>
>
>
> > Hello,
>
> > I'm new to using VBA in Excel. =A0I'm hoping I'm missing something
> > simple and that someone here can help me.
>
> > I have one Excel workbook that has many subroutines/macros in Module
> > 1. =A0I have 5 worksheets in the workbook and each has navigation
> > buttons to move from sheet to sheet. =A0On one sheet, there are require=
d
> > fields that must be checked before a user can be moved to the desired
> > sheet. =A0The buttons call the appropriate macro, like GoTo_Report,
> > which takes the user to the Report sheet. =A0But, on the Project Info
> > screen, I need all these buttons to also run the macro that checks
> > against null values in the required fields. =A0I have that code in
> > ProjInfoReqFields.
>
> > ProjInfoReqFields has 8 IF statements. =A0I want each of these 5 button=
s
> > to call ProjInfoReqFields, have it run through all 8 IF statements,
> > then call the second macro to go to the appropriate sheet. =A0If I copy
> > this code in to each of the 5 navigation buttons, it works great. =A0It
> > goes through all 8 IF statements, then I have the final Else statement
> > calling the second macro. =A0But, when I try to just call both macros,
> > as shown below, it will only get through the first IF statement, then
> > it continues to the second macro.
>
> > Button2_Click()
>
> > 'Macro to check against null values on Project Info sheet
> > ProjInfoReqFields
>
> > 'Macro to move user from active sheet to Report sheet
> > GoTo_Report
>
> > End Sub
>
> > I made the following modification to the macro, which made it
> > successfully run through all 8 IF statements, but either wouldn't call
> > the second macro or something else is awry because it ends up going to
> > the wrong sheet.
>
> > Button2_Click()
>
> > 'Macro to check against null values on Project Info sheet
> > ProjInfoReqFields
> > Exit Sub
>
> > 'Macro to move user from active sheet to Report sheet
> > GoTo_Report
>
> > End Sub
>
> > I appreciate any help.
> > Thanks!
> > Tina- Hide quoted text -
>
> - Show quoted text -

0
dmbluv (2)
8/18/2008 12:14:45 PM
Of course, if your field needs filling in you will need to run the sub AGAIN 
to re-check. If all is OK, you are then taken to your other sheet.

Sub ProjInfoReqFields()
myarray = Array("d9", "d11", "d13", "d16")

For Each c In myarray
    'MsgBox Range(c).Value
If Len(Application.Trim(Range(c))) < 1 Then
MsgBox "Cell " & Range(c).Address & " Not filled in"
Range(c).Select
Exit Sub
End If
Next c

Application.Goto Sheets("Report").Range("a1")
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"dmbluv" <dmbluv@comcast.net> wrote in message 
news:28b14858-dfc3-4af8-a0bc-b8ac2ac93823@e39g2000hsf.googlegroups.com...
Don,

Unfortunately, my code is on a different system and I can't copy it
here.  But, to give you an idea, I've typed up a quick example below:

Sub ProjInfoReqFields()

If Range("D9") = "" Then
  Msgbox "This field is required."
  Range("D9").Select
Else
  If Range("D11") = "" Then
    Msgbox "This field is required."
    Range("D11").Select
  Else
    If (so on for another 6 cells)

For purposes of full-disclosure, or as much as I can, here is the code
I have to move the user to the Report sheet, which is called as the
second macro for Button2_Click:

Sub GoTo_Report()
'Moves user to Report screen
Sheets("Report").Select
Range("A1").Select
End Sub

When I call ProjInfoReqFields from another macro, such as
Button2_Click, it will display the message box for D9, and probably
selects D9 as well (too quick for me to tell), but then immediately
moves on to the second macro.  See below:

Button2_Click()
'Macro to check against null values on Project Info sheet
ProjInfoReqFields
'Macro to move user from active sheet to Report sheet
GoTo_Report
End Sub

If I modify it to add Exit Sub after calling ProjInfoReqFields, it
does in fact cycle through all the IF statements, but then does not
run the second macro to take user to desired sheet; it actually takes
user to a sheet that is not called in any macro.  (That's why I think
it's exiting the subroutine before running the second macro.)  See
below:

Button2_Click()
'Macro to check against null values on Project Info sheet
ProjInfoReqFields
Exit Sub
'Macro to move user from active sheet to Report sheet
GoTo_Report
End Sub

Thanks for any guidance!
Tina

On Aug 16, 4:59 pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> Might help if you post all of your code for comments
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"dmbluv" <dmb...@comcast.net> wrote in message
>
> news:fbabc9b5-8ca9-4082-a8a9-9419f7f5c521@y21g2000hsf.googlegroups.com...
>
>
>
> > Hello,
>
> > I'm new to using VBA in Excel. I'm hoping I'm missing something
> > simple and that someone here can help me.
>
> > I have one Excel workbook that has many subroutines/macros in Module
> > 1. I have 5 worksheets in the workbook and each has navigation
> > buttons to move from sheet to sheet. On one sheet, there are required
> > fields that must be checked before a user can be moved to the desired
> > sheet. The buttons call the appropriate macro, like GoTo_Report,
> > which takes the user to the Report sheet. But, on the Project Info
> > screen, I need all these buttons to also run the macro that checks
> > against null values in the required fields. I have that code in
> > ProjInfoReqFields.
>
> > ProjInfoReqFields has 8 IF statements. I want each of these 5 buttons
> > to call ProjInfoReqFields, have it run through all 8 IF statements,
> > then call the second macro to go to the appropriate sheet. If I copy
> > this code in to each of the 5 navigation buttons, it works great. It
> > goes through all 8 IF statements, then I have the final Else statement
> > calling the second macro. But, when I try to just call both macros,
> > as shown below, it will only get through the first IF statement, then
> > it continues to the second macro.
>
> > Button2_Click()
>
> > 'Macro to check against null values on Project Info sheet
> > ProjInfoReqFields
>
> > 'Macro to move user from active sheet to Report sheet
> > GoTo_Report
>
> > End Sub
>
> > I made the following modification to the macro, which made it
> > successfully run through all 8 IF statements, but either wouldn't call
> > the second macro or something else is awry because it ends up going to
> > the wrong sheet.
>
> > Button2_Click()
>
> > 'Macro to check against null values on Project Info sheet
> > ProjInfoReqFields
> > Exit Sub
>
> > 'Macro to move user from active sheet to Report sheet
> > GoTo_Report
>
> > End Sub
>
> > I appreciate any help.
> > Thanks!
> > Tina- Hide quoted text -
>
> - Show quoted text -

0
dguillett1 (2487)
8/18/2008 12:54:12 PM
Reply:

Similar Artilces:

Question about what "Home" means in the Home and Student Edition
Hello All<br> <br> I was wondering if someone could answer a question for me please.<br> <br> I am seriously considering buying the "Office 2008 for Mac - Home and Student Edition" and I want to check what is allowed by the EULA.<br> <br> I like the 3 licenses because both my wife and I have a Mac and would be able to use Office 2008.<br> <br> However, both of us receive both "work" related email as well as "personal/home" email (we each use one email address for everything). Also, occasionally we might write a �work...

Outlook 2002 "The remote Procedure call Failed"
I have found many posting of the error occuring but none of them have answered my situation. System Specs: OS: WinXP Pro SP1 (can't install SP2 until updates for CAM software are created) Office: Office 2002 updated (Server running Exchange 2003 Stand.) AV: Norton Corp v8.0 updated Situation: Open Outlook/ create new mail / click the TO: button... system waitings and reports "The remote Procedure call Failed" and Outlook shuts down... I know that there is no virus on this computer many different AV products scans confirms this. Client currently using OWA to email... Any th...

Closing another database with VBA
Hi, I’m having a problem with the code to do the following… 1- A user opens a file called Core. 2- Upon opening the splash screen (on the Form_Load event), it compares a version number it finds within the database to one that is in another database called Data. 3- If the versions are different, the Core file opens the Updater file. 4- The Updater file closes the Core file. 5- The Updater file copies the latest version of the Core file (located somewhere else) and replaces the first one with the latest version. 6- The Updater opens the newly copied Core file. 7- The Updater closes itself. ...

macro for comparing fields in two work books
Hello, I have two excel spread sheets (say A1 & A2). A1- is the master sprea sheet and A2 is smaller spread sheet with very few details. Suppose Column B in A2 has 100 partnumbers and Column C in A1 has th superset of partumbers (1000) and corresponding details for each par number, I need to: 1. Check if all the 100 part numbers in A2 has a corresponding match i A1 2. Extract the info for the matched partnumbers from A1 and list in seprate sheet. 3. Even if there in no match A1, let's say for 40 of them, it shoul still list those parts in the new sheet. Can anybody help me with t...

Out of Office Question
We're new to O at our office. On occasion, I get OOO replies when I email people. I ask how they set it up and everyone tells me Tools, then Out of Office. My choices under Tools do not include OOO but they do include Rules Wizard. I feel sure somewhere in there is a way to set up OOO replies but I haven't hit upon the exact procedure. Help is no help at all. I have the disc, I run W2000 Professional. Can someone give me the drill to set up an OOO reply so I can turn it on before I go on vacation next week? Thanks in advance to all who help or try. Steve Ignots Steve, You need to be u...

VBA Autocomplete
I am having trouble with autocomplete that has stop working in the IDE while coding. Then it will suddenly start again for a while, before stopping... I have tried restarting excel and the PC but no joy Any ideals what causes this? Win 2000 Excel 2000 VBA IDE 6.0.8714 ...

shift and down keys in macro
Hi, I want to mark four cells. How can I write shift+down+down+down in a macro. (Then I say copy paste etc). Thanks Cousin Excel Activecell.Resize(4).Select Activecell.Resize(4).Copy OR starting from cell A1 copy 4 cells to the same sheet cell J1 Range("A1").Resize(4).Copy Range("J1") -- Jacob "CousinExcel" wrote: > Hi, > I want to mark four cells. > How can I write shift+down+down+down in a macro. > (Then I say copy paste etc). > Thanks > Cousin Excel Hi and thank you Jacob Skaria. This was something new for me...

How to stop tracking a Contact in CRM 4.0
How do I stop tracking an Outlook Contact that has been set to "Track in CRM" Hi Ken, The "Track in CRM" for contacts turns to "View in CRM" when a matching contact record is created in the CRM database. If you no longer want that contact in CRM then you can delete it, then it will say "Track in CRM" again. "Ken Florian" wrote: > How do I stop tracking an Outlook Contact that has been set to "Track in CRM" ...

PDF question
Our (non-profit) organization puts out a newsletter (created in Publisher), and we'd like to convert it to PDF to send it to some people that way. I know how to do this (I have Primo PDF), but when I just did it, the fonts in (small) sections on two of the pages were garbled. In one instance it was an unusual font, so I thought that might be the problem. But in the other instance it's not an unusual font. Is there any way I can change/correct the garbled font? I have Adobe Acrobat reader 5.0. Seems like I might have tried to upgrade a while back and it didn't "ta...

Calender related question
Is there a way to tell what time and date an appointment was created. We have Outlook 2003 with Exchange 2003 and Windows 2003 Server. Thank you In outlook, you can add the "Created" column and that will show the create time for that item. "Vic Abrahamian" <VicAbrahamian@discussions.microsoft.com> wrote in message news:4EC32A4D-4C7B-46A0-862B-A5F165AECCDE@microsoft.com... > Is there a way to tell what time and date an appointment was created. > We have Outlook 2003 with Exchange 2003 and Windows 2003 Server. > Thank you ...

Update stored procedure question
We are using VS 2008 and SQL Server 2005. I have a table Spread that has 1 Integer column called SecurityID. I have a stored procedure spUpdateSpread that increment the SecurityID by 1, and returns that value. If more than 1 process call the stored procedure at the same time, will it return the correct SecurityID ? Thank you CREATE TABLE [dbo].[Spread]( SecurityID [int] NOT NULL, PRIMARY KEY NONCLUSTERED ( [SecurityID] ASC ) ) ON [PRIMARY] CREATE PROCEDURE [dbo].[spUpdateSpread] @SecurityID int output as update Spread set SecurityID = SecurityID + 1 select @Secu...

Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English to Metric conversion routines in the Access VBA code and would like to run a query on the data that will return coverted values. I need to be able to execute this from outside the database (run the query from another program). Yes. The function must be a Public Function in a standard module. You use a calculated control to return the results of the query. ConvertedValue: MyFunction([SomeField]) -- Dave Hargis, Microsoft Access MVP "DavidY" wrote: > Can I call a VBA subroutine from within an ...

rules question
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C58E9B.93495960 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I'd like to make a rule similar to the one I have - move all emails from a certain SENDER to a specified FOLDER. I want to also make a rule to move to a specified folder, when an email is sent to a specific USER. I don't see how to do this in the canned rules? Has anyone got creative with this and made it work? I can get it to do a COPY, but not a move. Thanks!!! ------=_NextPart_000_0008_0...

SQL select statement question
My dataset has 3 columns: customer id, store location, charges I'd like to write a single select statment that will show each customer id once, with the store location where they have the most charges, and the sum of the charges for that store location. This gives the max charges for each customer, but doesn't give the location: select a.custID, max(new.amt) from smallchg a inner join; (select custID, location, sum(charges) as amt from smallchg group by custID, location) as new ; on a.custID= new.CustID group by a.CustID thanks. joel Try this -- SELECT TOP 1 ...

macro protection problem
What is the preferred method to deal with macro protection? I have just updated all the machines to office2k3 and now a critical worksheet that uses benign macros doesn't run because of the protection. Bringing security to "low" allows it to run but each user has to set that themselves. I have heard about creating a local CA for trusting the macro and I am interested in that but isn't there an easier way? Setting security to low is very unwise. Stupid even, in a company. All methods involve making changes on each machine unless you have Group Policies set up (which...

Shared Contacts Master Categories question
I need to have 50 users on Outlook/Exchange specify a number of contacts that need to be exported to another system. I had my Exchange admin give me shared access to each of the mailboxes in question. My plan was to go through each set of contacts, and add a new category to the master categories for each user. When I got the the second contact, the keyword identifier was already there! It looks like Outlook/Exchange is allowing me access to the correct contacts, but is using my master category list. How can I access and update each users category master list? Isn't this kept ...

Newbie question, can't get a handle on the handles <g> ... ?
How do we manipulate text that needs to flow to another box, i.e., when working in two columns and we need text to flow to a third, how do we do this? I'm sorry for such a simple question, but I've looked through the help and on the web and I think it's because I don't know what it's called. Anyhoo, pls advise! Tx. Whilst attempting to develop brick-based storage technology, Ed reads a message from Dylan56 <No@SpamAtAll.com> > How do we manipulate text that needs to flow to another box, i.e., > when working in two columns and we need text to flow to a third,...

Macro to open print window and set to print entire workbook
I need help getitng a macro created to open the print window, then se it to 'print the entire workbook'. Then the macro would stop. At whic the user would then specify additional specs for printing. I.E. # o pages, paper size etc. I am trying to default the 'print entire work book option' withou restricting the user from other print specs. I have a macro that will print once a button is clicked on th spreadsheet but it goes directly to print and does not let the user se other specs. I.E. # of pages, etc. Thanks in advanc -- retseor ---------------------------------------...

WLM Stopped working 02-20-10
I'm running Vista Home Premium, SP2, 2 GB of RAM, Firefox, WLM, all WU's current. I was in WLM mail newsgroups earlier today and things just stopped. WLM stopped responding. Now I cannot log into WLM. It used to log into window Live by itself (password and all) from my desktop icon, but now the only thing that happens when I try to open my WLM app window is I'm taken to IE (7). Then when when I try to sign in Windows live it won't do it. I've already ran SAS, MBAM, CCleaner and Avira in Safe mode but found nothing. Then I did a system restore back to a fe...

checking for existance of file before running macro
I have a macro that opens a source file to retrieve information. I want to be able to check to see if it's there first and if it's not send a msg to the user stating that the file isn't there. When the user clicks okay on the box I want the macro to end without error. Can anyone help? thx, Jim You can use the Dir() function: dim myFilename as string myfilename = "C:\autoexec.bat" if dir(myfilename) = "" then 'it's not there else 'yes, it is end if Jim wrote: > > I have a macro that opens a source file to retrieve information. I wan...

Entourage has stopped retrieving just one certain person's email
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Email Client: pop I have been receiving all my emails normally apart from just one sender, who i previously had no problems with. The email is sitting in my inbox when i log in via the browser account, however it just doesnt get sent directly from the sender. if I then send this person's email to myself, it gets delivered to my inbox. THe problem ahs only started in the last week, no settings have been changed and every other email is getting delivered. <br> My hosting provider says it is not their issue and they don...

Macro to "save as"
Hi, Am using excel 2003 and am wanting to create a macro, attached to a button, that will save as a particular cell #, say A2, and then print. Have the print down pat, but can't figure out the save as part....... -- Thanks for the help What is in cell A2? Try things like these, depending on what you have and what you want to do.... ThisWorkbook.SaveAs Range("A2").Value & ".xls" ThisWorkbook.SaveCopyAs Range("A2").Value & ".xls" ThisWorkbook.SaveAs "C:\Folder Path\" & Range("A2").Value & ".xls", Ad...

VBA Command to Deselect a Shape
I've written a VBA program that drops shapes (from a stencil I created) on a page, but at the end of the exercise the last shape is selected (it has a dotted line surrounding it). Is there a VBA command that will deselet this shape (or select the page on which it sits? Mike On Fri, 22 Sep 2006 12:16:02 -0700, MikeM <MikeM@discussions.microsoft.com> wrote: >I've written a VBA program that drops shapes (from a stencil I created) on a >page, but at the end of the exercise the last shape is selected (it has a >dotted line surrounding it). Is there a VBA command that ...

Advanced Filters question
I sometimes use Msft Access to run a query, and then revise the query, or build off of the new query by creating a separate query of that query. However, in using excel, at least at first pass, I noticed that I was unable to pivot the data that resulted from an advanced filter, and I was unable to see the total amount for a given column, once an advanced filter was applied to a list (i.e., the sum that I saw was for the entire field and not for the records that were visible once I had applied the filter). Does anyone have any perspective they could share with me regarding this? Is there a wa...

Mail (outlook 2002), 'To' field, general question
Why do some names of some messages in the 'To' Field appear with single quotes in my Sent Box? I'm using Outlook 2002. I'm sure there's an easy explanation but it stumps me. ...