Out of Stack Space ... Recorded Macros

Excel2003 ... I know nothing of writing Code ... I Record Macros only ...

That said ... SS working fine until I started getting error (Out of Stack 
Space)

Is there "HELP" for me??? ... Thanks ... Kha


0
Utf
4/14/2010 5:29:02 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
1231 Views

Similar Articles

[PageSpeed] 33

SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting 
"Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 
Calculate???

Sub Sort_Type_Rec_Ascend()
'
' Sort_Type_Rec_Ascend Macro
'

'
    a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!")
    If a = 7 Then Exit Sub
    
    ActiveSheet.Unprotect
    
    Range("A2:D22").Select
    Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowFormattingRows:=True, AllowSorting:=True, 
AllowFiltering:=True
        
    Calculate
        
    Range("O40").Select
    
End Sub


Thanks ... Kha

"Ken" wrote:

> Excel2003 ... I know nothing of writing Code ... I Record Macros only ...
> 
> That said ... SS working fine until I started getting error (Out of Stack 
> Space)
> 
> Is there "HELP" for me??? ... Thanks ... Kha
> 
> 
0
Utf
4/14/2010 5:36:01 PM
Is your workbook set to manual calculate?  If not, then I'd just delete the 
offending Calculate statement from the code.

Although I doubt if it has anything to do with it, the code always works 
with the ActiveSheet.  That is, the sheet currently selected.  Is the sheet 
this code was designed for the active sheet when it's run?  Again, shouldn't 
have anything to do with out of stack problem - that's usually caused by an 
almost infinite loop reiteratively calling a routine/process.

"Ken" wrote:

> SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting 
> "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 
> Calculate???
> 
> Sub Sort_Type_Rec_Ascend()
> '
> ' Sort_Type_Rec_Ascend Macro
> '
> 
> '
>     a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!")
>     If a = 7 Then Exit Sub
>     
>     ActiveSheet.Unprotect
>     
>     Range("A2:D22").Select
>     Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
>         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
>         DataOption1:=xlSortNormal
>         
>     ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
>         True, AllowFormattingRows:=True, AllowSorting:=True, 
> AllowFiltering:=True
>         
>     Calculate
>         
>     Range("O40").Select
>     
> End Sub
> 
> 
> Thanks ... Kha
> 
> "Ken" wrote:
> 
> > Excel2003 ... I know nothing of writing Code ... I Record Macros only ...
> > 
> > That said ... SS working fine until I started getting error (Out of Stack 
> > Space)
> > 
> > Is there "HELP" for me??? ... Thanks ... Kha
> > 
> > 
0
Utf
4/14/2010 6:25:01 PM
Yes ... WB 21mg so I set Calc to "Manual" for speed of data entry ... Then I 
forced a "Calulate" in Recorded Macro ... ALL working fine when all of a 
sudden I had "Out of Stack Space" error message with "Calculate" the yellow 
high-lite offender.

SS appears to still be working fine & I can "F9" Calculate with no problem 
.... I just can't Macro Record "F9" as "Calculate" & then assign a Forms 
Button to it ... as this is when the "Calculate" returns the "Out of Stack 
Space" Error.

Issue is ... this SS to be updated by other Folks ... Setting Calc to Auto 
will slow data entry down ... & F9 Calc is not a key I am confident they will 
use when they should ... Consequently, I buried the "Calculate" within the 
Macro Buttons, but now a problem???

Thanks for listening & also for supporting these boards ... Any further 
suggestions here are welcome ... Kha 


"JLatham" wrote:

> Is your workbook set to manual calculate?  If not, then I'd just delete the 
> offending Calculate statement from the code.
> 
> Although I doubt if it has anything to do with it, the code always works 
> with the ActiveSheet.  That is, the sheet currently selected.  Is the sheet 
> this code was designed for the active sheet when it's run?  Again, shouldn't 
> have anything to do with out of stack problem - that's usually caused by an 
> almost infinite loop reiteratively calling a routine/process.
> 
> "Ken" wrote:
> 
> > SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting 
> > "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 
> > Calculate???
> > 
> > Sub Sort_Type_Rec_Ascend()
> > '
> > ' Sort_Type_Rec_Ascend Macro
> > '
> > 
> > '
> >     a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!")
> >     If a = 7 Then Exit Sub
> >     
> >     ActiveSheet.Unprotect
> >     
> >     Range("A2:D22").Select
> >     Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
> >         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> >         DataOption1:=xlSortNormal
> >         
> >     ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
> >         True, AllowFormattingRows:=True, AllowSorting:=True, 
> > AllowFiltering:=True
> >         
> >     Calculate
> >         
> >     Range("O40").Select
> >     
> > End Sub
> > 
> > 
> > Thanks ... Kha
> > 
> > "Ken" wrote:
> > 
> > > Excel2003 ... I know nothing of writing Code ... I Record Macros only ...
> > > 
> > > That said ... SS working fine until I started getting error (Out of Stack 
> > > Space)
> > > 
> > > Is there "HELP" for me??? ... Thanks ... Kha
> > > 
> > > 
0
Utf
4/14/2010 7:10:02 PM
Out of stack space normally has to do with a recursive call that never ends. 
Somewhere in the called function a variable is created. Because the call is 
recursive with no stopping point it just keeps creating these variables until 
the stack memory is out of space. 

To that end do you have calculate event code that is being called?

Perhaps try this...

Sub Sort_Type_Rec_Ascend()

    If MsgBox("SORT ... Type Record (Ascending)?", _
     vbYesNo, "ATTENTION!") = vbYes Then
    
        With ActiveSheet
        .Unprotect
        
        .Range("A2:D22").Sort Key1:=Range("A3"), _
                              Order1:=xlAscending, _
                              Header:=xlYes, _
                              MatchCase:=False
            
        .Protect
        .Calculate
        End With
    End If
End Sub

it only calculates the current sheet and not the entire workbook.
-- 
HTH...

Jim Thomlinson


"Ken" wrote:

> Yes ... WB 21mg so I set Calc to "Manual" for speed of data entry ... Then I 
> forced a "Calulate" in Recorded Macro ... ALL working fine when all of a 
> sudden I had "Out of Stack Space" error message with "Calculate" the yellow 
> high-lite offender.
> 
> SS appears to still be working fine & I can "F9" Calculate with no problem 
> ... I just can't Macro Record "F9" as "Calculate" & then assign a Forms 
> Button to it ... as this is when the "Calculate" returns the "Out of Stack 
> Space" Error.
> 
> Issue is ... this SS to be updated by other Folks ... Setting Calc to Auto 
> will slow data entry down ... & F9 Calc is not a key I am confident they will 
> use when they should ... Consequently, I buried the "Calculate" within the 
> Macro Buttons, but now a problem???
> 
> Thanks for listening & also for supporting these boards ... Any further 
> suggestions here are welcome ... Kha 
> 
> 
> "JLatham" wrote:
> 
> > Is your workbook set to manual calculate?  If not, then I'd just delete the 
> > offending Calculate statement from the code.
> > 
> > Although I doubt if it has anything to do with it, the code always works 
> > with the ActiveSheet.  That is, the sheet currently selected.  Is the sheet 
> > this code was designed for the active sheet when it's run?  Again, shouldn't 
> > have anything to do with out of stack problem - that's usually caused by an 
> > almost infinite loop reiteratively calling a routine/process.
> > 
> > "Ken" wrote:
> > 
> > > SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting 
> > > "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 
> > > Calculate???
> > > 
> > > Sub Sort_Type_Rec_Ascend()
> > > '
> > > ' Sort_Type_Rec_Ascend Macro
> > > '
> > > 
> > > '
> > >     a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!")
> > >     If a = 7 Then Exit Sub
> > >     
> > >     ActiveSheet.Unprotect
> > >     
> > >     Range("A2:D22").Select
> > >     Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
> > >         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> > >         DataOption1:=xlSortNormal
> > >         
> > >     ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
> > >         True, AllowFormattingRows:=True, AllowSorting:=True, 
> > > AllowFiltering:=True
> > >         
> > >     Calculate
> > >         
> > >     Range("O40").Select
> > >     
> > > End Sub
> > > 
> > > 
> > > Thanks ... Kha
> > > 
> > > "Ken" wrote:
> > > 
> > > > Excel2003 ... I know nothing of writing Code ... I Record Macros only ...
> > > > 
> > > > That said ... SS working fine until I started getting error (Out of Stack 
> > > > Space)
> > > > 
> > > > Is there "HELP" for me??? ... Thanks ... Kha
> > > > 
> > > > 
0
Utf
4/14/2010 8:17:22 PM
Just curious if you have any worksheet/workbooks/application events that are
firing when your code runs?



Ken wrote:
> 
> SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting
> "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9
> Calculate???
> 
> Sub Sort_Type_Rec_Ascend()
> '
> ' Sort_Type_Rec_Ascend Macro
> '
> 
> '
>     a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!")
>     If a = 7 Then Exit Sub
> 
>     ActiveSheet.Unprotect
> 
>     Range("A2:D22").Select
>     Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
>         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
>         DataOption1:=xlSortNormal
> 
>     ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
>         True, AllowFormattingRows:=True, AllowSorting:=True,
> AllowFiltering:=True
> 
>     Calculate
> 
>     Range("O40").Select
> 
> End Sub
> 
> Thanks ... Kha
> 
> "Ken" wrote:
> 
> > Excel2003 ... I know nothing of writing Code ... I Record Macros only ...
> >
> > That said ... SS working fine until I started getting error (Out of Stack
> > Space)
> >
> > Is there "HELP" for me??? ... Thanks ... Kha
> >
> >

-- 

Dave Peterson
0
Dave
4/14/2010 11:01:01 PM
Jim / Dave ... Thanks for responding ...

Unfortunately, I can not respond to Jim's reply because I simply do not 
understand any VB Code language or terminology ... :(

That said ... I had a recorded Macro named "Calculate" & I had Form Buttons 
attached to it ... I also had "Calculate" as an instruction within other 
Recorded Macros ... Since yesterday, I killed the "Calculate" Macro & I 
re-recorded it with a different Name "Calculate File" ... I re-assigned 
Buttons to this Macro & I no longer get "Stack" error when using these 
buttons ... This is good.  Now I am going to take the apostrophe away from in 
front of the "Calculate" instruction that is within the other Macros to see 
if I am now around the "Stack" error???

Not sure if any of this will work, but in reading Jim's reply & his term 
"looping" I wondered if somehow the Macro "Calculate" & my imbedded 
"Calculate" instruction were somehow causing this???

Thanks for supporting these boards & providing the many valuable solutions 
.... Kha 


"Dave Peterson" wrote:

> Just curious if you have any worksheet/workbooks/application events that are
> firing when your code runs?
> 
> 
> 
> Ken wrote:
> > 
> > SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting
> > "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9
> > Calculate???
> > 
> > Sub Sort_Type_Rec_Ascend()
> > '
> > ' Sort_Type_Rec_Ascend Macro
> > '
> > 
> > '
> >     a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!")
> >     If a = 7 Then Exit Sub
> > 
> >     ActiveSheet.Unprotect
> > 
> >     Range("A2:D22").Select
> >     Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
> >         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> >         DataOption1:=xlSortNormal
> > 
> >     ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
> >         True, AllowFormattingRows:=True, AllowSorting:=True,
> > AllowFiltering:=True
> > 
> >     Calculate
> > 
> >     Range("O40").Select
> > 
> > End Sub
> > 
> > Thanks ... Kha
> > 
> > "Ken" wrote:
> > 
> > > Excel2003 ... I know nothing of writing Code ... I Record Macros only ...
> > >
> > > That said ... SS working fine until I started getting error (Out of Stack
> > > Space)
> > >
> > > Is there "HELP" for me??? ... Thanks ... Kha
> > >
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
4/15/2010 2:36:01 PM
Jim / Dave ... (Good morning)

Yes ... I am back on the road again ... All Buttons & Macros working as 
desired ... NO more "Out of Stack Space" issue with "Calculate" ... :)

Many "Thanks" again ... Kha

"Ken" wrote:

> Jim / Dave ... Thanks for responding ...
> 
> Unfortunately, I can not respond to Jim's reply because I simply do not 
> understand any VB Code language or terminology ... :(
> 
> That said ... I had a recorded Macro named "Calculate" & I had Form Buttons 
> attached to it ... I also had "Calculate" as an instruction within other 
> Recorded Macros ... Since yesterday, I killed the "Calculate" Macro & I 
> re-recorded it with a different Name "Calculate File" ... I re-assigned 
> Buttons to this Macro & I no longer get "Stack" error when using these 
> buttons ... This is good.  Now I am going to take the apostrophe away from in 
> front of the "Calculate" instruction that is within the other Macros to see 
> if I am now around the "Stack" error???
> 
> Not sure if any of this will work, but in reading Jim's reply & his term 
> "looping" I wondered if somehow the Macro "Calculate" & my imbedded 
> "Calculate" instruction were somehow causing this???
> 
> Thanks for supporting these boards & providing the many valuable solutions 
> ... Kha 
> 
> 
> "Dave Peterson" wrote:
> 
> > Just curious if you have any worksheet/workbooks/application events that are
> > firing when your code runs?
> > 
> > 
> > 
> > Ken wrote:
> > > 
> > > SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting
> > > "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9
> > > Calculate???
> > > 
> > > Sub Sort_Type_Rec_Ascend()
> > > '
> > > ' Sort_Type_Rec_Ascend Macro
> > > '
> > > 
> > > '
> > >     a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!")
> > >     If a = 7 Then Exit Sub
> > > 
> > >     ActiveSheet.Unprotect
> > > 
> > >     Range("A2:D22").Select
> > >     Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
> > >         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> > >         DataOption1:=xlSortNormal
> > > 
> > >     ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
> > >         True, AllowFormattingRows:=True, AllowSorting:=True,
> > > AllowFiltering:=True
> > > 
> > >     Calculate
> > > 
> > >     Range("O40").Select
> > > 
> > > End Sub
> > > 
> > > Thanks ... Kha
> > > 
> > > "Ken" wrote:
> > > 
> > > > Excel2003 ... I know nothing of writing Code ... I Record Macros only ...
> > > >
> > > > That said ... SS working fine until I started getting error (Out of Stack
> > > > Space)
> > > >
> > > > Is there "HELP" for me??? ... Thanks ... Kha
> > > >
> > > >
> > 
> > -- 
> > 
> > Dave Peterson
> > .
> > 
0
Utf
4/15/2010 2:53:02 PM
Reply:

Similar Artilces:

Can not get Excel macros to save chart format -lost in space!
Help??? I've been searching this forum for 5 days trying to find a problem similar to what I am having without any luck. I have been trying to use Excel macro to record the creation, formating and saving of a simple bar chart against my Excel data range but the macros do not run for the chart. I am able to creat the charts okay but the macro craps out when I run it. I keep getting " Set ActiveChart = ActiveSheet.ChartObjects(Chart1)" type errors as if it's not recognizing the chart that I just made. I am an advanced Excel person BUT not so with VB coding. ...

CRM 4.0 records reverting to old state; caused by upgrade, rollup?!?
Hi all, A quick timeline before explaining my issue: - We installed CRM 3.0 a couple of years back. We imported old data from ACT! and used a custom drop down field with the value "Migrated from ACT!" to distinguish. - Users have put in a lot of work cleaning up those records, merging when necessary, assigning to right owners... - At some point we installed rollup 1 for 3.0 successfully. - Last year, mid-December, we upgraded to CRM 4.0 (I believe) successfully. - A month later, mid-January, we installed rollup 1 for CRM 4.0. - A few months later, some users were complaining about d...

Help creating two listings from one record
I have extracted "members" from my database. One record will contain LastName and FirstName, and may also contain SpouseName. Because I am creating membership cards in Publisher, I think I probably should actually create another query or ?? that contains one record with either [FirstName LastName] or [SpouseName LastName] Probablyl something like ... If SpouseName greater spaces List SpouseName LastName else next sentence. List FirstName LastName I don't have a clue how to do this in Access. Appreciate all help. Try this -- [FirstName] &a...

Extracting macros from a worksheet #3
Gord, Thanks for the info. I have Excel setup to ask me about enabling and disabling macros when opening a worksheet. I've tried both with no luck. I get the same error when attempting to open this file. The workbook that I'm opening has always worked before. That workbook and macros were designed and written by me and they have been working for more that 5 months now. There are only two worksheets in the workbook and I've never had any reason to group them together, however, when the workbook opens with the error, I can see that the two sheets are, in fact, selected. In add...

Make (save) a file by using macro recorder.
Hi all, I am new to macro and using macro recoder to create macro. On one file which I recieve every week from material department. I need to reformat and create pivot table. I use macro recorder and everything is working good. However I need that when my recorded macro end it create a file with the same name with the date and time it was create. I need every week a new file and I dont want to replace the one. Please let me know how I can create a new file with the date and time everyweek when I run my macro. Thanks -- Farhan Hi Farhan This should do it, just insert you...

Recording a charitable donation in MSM 2005
How do I record a charitable donation in MSM 2005? There is no one answer. You also provide precious little context to give a precise answer. Many people assign the transaction to some expense category like "Charitable Donation:Cash" or "Charitable Donation:Non Cash". "Cary" <carygee@aol.com> wrote in message news:OKrvtsfaFHA.3048@TK2MSFTNGP12.phx.gbl... > How do I record a charitable donation in MSM 2005? Dick Watson wrote: > There is no one answer. You also provide precious little context to give a > precise answer. > > Many peop...

Combining Stack bar and Line Charts
I'm trying to display 2 sets of data within the same chart; I want to display yr 1 data in a stacked bar format and yr 2 data in a line format. Both data sets would use the same axis; month and volume. I need to distinctively display each mo/yr together to show any increases or decreases in volume. Any suggestions you have are appreciated! -- tibor ------------------------------------------------------------------------ tibor's Profile: http://www.exceltip.com/forum/member.php?action=getinfo&userid=156 View this thread: http://www.exceltip.com/forum/showthread.php?threadid=1...

Duplicate the Record in a Form and many SubForms
I am using Allen Browne's tip that is available in his web site. However, I get a compile error stating that the variable dbFailOnError is not defined. I have search the MS Knowdledgeable base and found out the DBEngine (0) (0).Execute is no longer used in current versions. I change it to the following but I still get the same compile error message. If Me.SF_Adjacencies.Form.RecordsetClone.RecordCount > 0 Then strSql = "INSERT INTO [Adjacencies] ( DataRmID, AdjID,_ AdjRoomName, AdjRelevance ) " & _ "SELECT " & lngID &...

Copy data from Word macro to excel
I am working on a combined Word and Excel sheet (Both 2000 version) What I am trying to do is capture information in Word in an Userform The information that is entered in the form is then used to fill out word document that can be send to a customer. However this information also needs to be entered into an excel sheet currently that is done manually. But I would like to automate that wit a macro. The data that is being captured is in the following form: -If bolOKButtonPressed Then WordBasic.SetDocumentProperty "ProjectTitle", 0 UserForm1.ProjectTitle.Value, 2 WordBasic.SetD...

Using a listbox selection to limit records in a form and report
I am trying to use a listbox of names to limit the results of a form to just those names. On a similar track, I want to use the selected names to limit a report. I have the listbox created and have added a button next to it to initiate the next step, but cannot figure out how to do it. Also, would like to (if possible) use multiple listboxs in the same way (ie, name and/or department). "dascooper" <dascooper@discussions.microsoft.com> wrote in message news:1227B7CC-3345-4FDC-8EB0-94BEF46660FD@microsoft.com... >I am trying to use a listbox of names to limit the resu...

Out of Stack Space ... Recorded Macros
Excel2003 ... I know nothing of writing Code ... I Record Macros only ... That said ... SS working fine until I started getting error (Out of Stack Space) Is there "HELP" for me??? ... Thanks ... Kha SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 Calculate??? Sub Sort_Type_Rec_Ascend() ' ' Sort_Type_Rec_Ascend Macro ' ' a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!&...

Macro Assigned to Tool Bar Button
How can I tell what Macro is assigned to a tool bar button? Thanks Right-Click the Toolbar and select Customize. Then Right-Click the button and select Assign Macro... "C" <C@discussions.microsoft.com> wrote in message news:7ADB30E4-7ADD-43B8-8696-53C97C9D4100@microsoft.com... > How can I tell what Macro is assigned to a tool bar button? > > Thanks Thanks Jim. That was fairly simple. "Jim Spiller" wrote: > Right-Click the Toolbar and select Customize. Then Right-Click the button > and select Assign Macro... > >...

subtotal
I am using Excel 2003, recently upgraded to service pack 2. I have a macro that opens a delimited text file, I then copy all, paste values and number formats and then run a macro that subtotals a balance by change in every consumer. The problem is that when, upgraded to service pack 2, the last record does not subtotal, all other records do. I have tried copying only the "live" data after the marco opens the text file. Still the same error. I have tried pasting different ways - values, normal paste, paste values and number formats. Same thing happens - the GRAND TOTAL - ends u...

Run macro automatically.
How do I make a macro run automatically when a worksheet it is attached to is loaded? right click on the sheet tab>view code>left window worksheet>right window activate -- Don Guillett SalesAid Software donaldb@281.com "Excel macro" <Excel macro@discussions.microsoft.com> wrote in message news:DD7AF1E3-9263-4523-AC49-A43ABA1AB9D0@microsoft.com... > How do I make a macro run automatically when a worksheet it is attached to is > loaded? I am unable to find "activate " when I right click on worksheet. ( i assume that you are referring to the windo...

Print records in continuous form
I have a Continuous Form that based on a Query that selects record according to the Month. What I want to do is to be able to print all the records in this Form by one Command button. So I have the report designed and I have created a Macro “Open Report” +Print Out, but it opens only the first record to print. I did the Where condition on the Command Button On Click: [PM#]=[Forms]![FormName]![PM#]. But it’s not working how I want it to work. How I can print all the records? Thanks for the help. I have tried that and it opens the report with all the records from the original table, bu...

Extracting Data in Cells in order -- (or) eliminating empty cell space in a column
Hi I have this problem that I bet is easy to solve, but i am lost. I am an expert at the slow way to do things, but maybe there is a better way. The only way I can describe the problem is by means of an example. Lets say I have a column of numbers: >_A_|_B_| etc >> 1_1_|___| 2_3_|___| 3_2_|___| 5_5_|___| 5_3_|___| 6_4_|___| 7_7_|___| 8_3_|___| 9_1_|___| and then i write a little function in the adjoing cell, B1: =if(a1=3,a2,"") From there I fill down column B to B9. OK, pretty simple so far, right? What I am looking for is instances where I find a '3' in co...

Last Record Numbering
I need to find a way to display in a form text box the highest numerical value entered in any one of four fields for the last record entered. For example: If Field_1 is 0001 Field_2 is 0008 Field_3 is 0005 Field_4 is 0004 When the form is opened, the textbox would display the number 0008. The textbox should display the highest of the four fields of the last record. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200708/1 Already responded to in another newsgroup. It is rarely necessary to post and re-post the same question in multiple ...

Combining records in access 2007
I have imported a text file into access 2007. However I guess when the person that exported the text files(i think they are from a sql server db), what was one record in the sql server db, is now broken down into two records in the text file. I am needing to try to take the text files once imported into a table in access to combine two records into one. I just have a autonumber field that is the primary key for each field. So my table looks like this(original_table): Column 1 Column 2 Column 3 1 ABC DEF 2 GHI JKL 3 ...

Clearing Combo Box When Advancing to a New Record
I have developed a survey with the following tables: TBL_RESPONDENT [RESP_ID] [SURV_ID] TBL_QUESTION [QUESTION_ID] [QUESTION] [SECTION_ID] [COMBO_ID] TBL_RESPONSE [RESP_ID] [QUESTION_ID] [RESPONSE] TBL_LKUP [COMBO_ID] [COMBO_VALUE] [SORT] In order to enter survey responses, I have developed a form (FRM_RESPONDENT) with a tab control consisting of five pages, each representing a different section within the survey. Each of the five pages is a sub form (FRM_RESPONSE_A, B, C, D, E) which is linked to FRM_RESPONDENT by the shared RESP_ID field on both the form and sub forms. The sub forms ...

removing spaces between the numbers
Hello all! When copying number from Navision account program to Excel, it copies numbers with spaces as text, but not as numerical values. Such value: 1 109 014,08 is copied as text. One possibility to set the number actually to a numerical value is to delete the spaces within the number, so after doing that it looks as 1109014,08. Then i can add, multiply, divide this number with ease. Is there a automatic possibility how remove those spaces? Is there an alterantive? Use Search and Replace - highlight all the cells, then Edit | Replace (or CTRL-H). In the Find box type a single spac...

Blank Spaces Help
Is there a way to make the spaces between the " "'s be determined by the length of characters of Sheet2!G9. What I need is a total of 8 characters including the spaces that precede the number (Sheet2!G9) . Example first number is 43 . I need the number of spaces to be 6 then my number Second number is 120. I need the number of spaces to be 5 then my number I was trying to use LEN but couldn't get anywhere. =CONCATENATE(" ",Sheet2!G9,Sheet2!H9) Any help suggestions would be appreciated T.I.A. dayton, try this formula which adds spaces to the beginning o...

Subform causes Main Form to show the same record multiple times
I have a form named Patients Info with a subform on it for Household Members. The Patients Info form is created from the table: Patients and the Household Members subform is created from the table:FedPovertyLevel. The table Patients is related to the table FedPovertyLevel in a one to many relationship. The problem I have is that when a second, third, etc Household Member is added, the button for the record selector on the main form now has to be clicked forward by the number of household members. For Example, if only one name is entered as a Household member, there is only ...

Record Selector out of Sync
Hi, I have a main form with two sub forms. The 1st subform displays a summary list, the main form displays the details of whatever record is selected in the 1st subform, and the 2nd sub form displays other related data. When a record is selected in the 1st subform, the main form and subform # 2 change to reflect the new record selected. I use the following code to do this: If Not IsNull(Me!ID) Then Parent.RecordsetClone.FindFirst "ID= " & Me.ID If Not Parent.RecordsetClone.EOF Then Parent.Bookmark = Parent.RecordsetClone.Bookmark End If End If The problem I am having...

Publisher 2002
I'm creating an Employee Directory (130 employees total, 4 employees per page) and cannot get the merge to move to the next record. Since the 'Catalog Merge' function isn't an option in 2002, and the company isn't going to buy 2003, can anyone help me do this in Publisher? Thanks, Kate Divide the page into 4. Input this number into the page setup. (2.75 inches), click the "change copies per sheet", change the gaps and margins to zero. This will give you 4 entries per page. Only one entry will be on your screen. Setup your merge. -- Mary Sauer MS MVP htt...

Maximum/Minimum number of records on import
My database is set to import a CSV. Is there a way to not allow a CSV file to be imported, and bring up a message box if the CSV file has less than 30 records or more than 500 records? I think it involves something to do with Select Case, but I'm not quite sure where to take it from there. Thank you for your response. I already have the actual importing process working perfectly the way I want it. But I was wondering whether or no there was some type of code to stick in there, to tell it not to allow importing of a CSV file with less than 30 or more than 500. If there is a code that I ca...