calculations running off data validation list

[UNDPC]

Hi,
I am working on a macro to cycle through a data validation list, calculate the sheet, then print each record each time. The problem I am having is that when I place the value from the list in the calucated cell dropdown, I cannot get the sheet to calculate before it runs the print portion of the macro. So basically it is just inserting value after value and printing the sheet without ever recalculating the sheet each time.

Does anyone know how I can accomplish this? I have tried pausing the macro for a time, then use the worksheet calculate event, but that has not worked. Any help is appreciated.

 

[PHV]

Have you tried this before printing ?
Application.Calculate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[UNDPC]

Yes, I have tried that as well and it does not work either. It still just prints and moves onto the next item in the list.

 

[SkipVought]

Please post your code.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[VBAjedi]

C'mon, Skipper - you ask far too much!

VBAjedi

 

[UNDPC]

Skip,
Here is my code. Thanks for you help.

Dim StartPage, n As Integer
Dim ListCell As String

StartPage = Val(InputBox("Enter the Starting Page Number.", "Page Number Start", "1"))
n = 69

ListCell = Range("AG" & n).Value 'this is where the list
is stored
Do Until IsEmpty(Range("AG" & n))
ListCell = Range("AG" & n).Value
Range("D5:J5").Select 'this is the dropdown
area
ActiveCell.FormulaR1C1 = ListCell
Application.Calculate
ActiveSheet.PageSetup.RightFooter = "&""Arial,Regular""&12 " & StartPage
StartPage = StartPage + 1
n = n + 1
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Loop

 

[SkipVought]

With Range("D5:J5")
  .FormulaR1C1 = ListCell
  .Calculate
End With

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[UNDPC]

Skip,
Should the code now look like this:

Do Until IsEmpty(Range("AG" & n))
ListCell = Range("AG" & n).Value
Range("D5:J5").Select
With Range("D5:J5")
.FormulaR1C1 = ListCell
.Calculate
End With
ActiveSheet.PageSetup.RightFooter
= "&""Arial,Regular""&12 " & StartPage
StartPage = StartPage + 1
n = n + 1
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Loop

 

[SkipVought]

Do Until IsEmpty(Range("AG" & n))
    With Range("D5:J5")
      .FormulaR1C1 = Range("AG" & n).Value
      .Calculate
    End With
    ActiveSheet.PageSetup.RightFooter  
      = "&""Arial,Regular""&12 " & StartPage
    StartPage = StartPage + 1
    n = n + 1
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Loop

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[UNDPC]

Skip,
Thanks for your help. In doing this, I have realized that the person who created this spreadsheet is only using the dropdown listbox (validation list) to populate another cell which is the parameter for an MS Access query from one of our databases. It looks like the query is downloaded to another sheet in the workbook. Each time a different item in the list is changed, it populates the parameter cell, and then connects to the DB to run the query based on the parameter in the cell. I have a feeling this is why my code is not working, it is not allowing time for the reconnect to DB and download of the new query info.

My problem is that I don't have much experience with connecting to the database via VBA. It seems like all that would need to be done would be to requery the DB to get the calculated fields to display the correct data downloaded from the query. Could you give me some insight on how to accomplish this?

If it helps, the name of the DB is "Portfolio Strats Secured.mdb" but i'm not sure what the name of the query is at this point, but will find out. Again, thanks for any help you can give me.

 

[PHV]

Make the BackgroundQuery property to False.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[UNDPC]

PH,
I don't see how this will help since it has to make the connection to the access DB to return the correct query data to the excel sheet. Doesn't turning that property off not allow for a requery?

 

[PHV]

I'm talking about the Excel's QueryTable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[UNDPC]

I was talking about the Excel table as well. Why would I want to turn off the background refresh property? I can't have static data, I need to retrieve new data each time the list item is changed. If I turn off the background refresh the requery won't run.

PH, you responded a bunch of times to my questions, but have never really explained anything in your answers.

Skip, if you're still reading this, I would appreciate your help because I'm not getting any from anyone else. You have always been very helpful. Thanks.

 

[PHV]

The BackgroundQuery property of a QueryTable object, when set to False forces Excel to wait for the query completion.
No <F1> key on your keyboard ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[UNDPC]

I'm sorry PH, I though you meant the Background Refresh property on the external data range where the query is downloaded to on the excel sheet. I'm still sort of a novice at this.

So would my code be something to this extent:

Sheets("List").QueryTable.BackgroundQuery = False

and insert this after retreiving the item from the list and before the Print step?

 

[PHV]

I though you meant the Background Refresh property on the external data range where the query is downloaded to on the excel sheet
You think right.
Don't allowing refresh in background doesn't means don't requery, but wait for all datas when refreshing.
connects to the DB to run the query
If this is done by code, like SomeQueryTable.Refresh, be sure to call this method with a parameter value of False.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244