Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining code from cmdClicks into 1 macro

    Hi Again:
    I'm using XL97-SR2. I have 3 sets of code associated with 3 cmdClick buttons that I want to combine into 1 set and 1 button so the user only has to click one button:
    BUTTON1 - Refreshes a query & returns data to Columns A:E;
    BUTTON2 - Clears F3:G6500 & copies the equations in F2:G2 down the length of the query Table (cols A:E);
    BUTTON3 - Refreshes a PT which gets its data from the query Table plus columns F & G.
    All 3 Buttons operate, independently, as they should. First, I try to combine 1&2 into cmdClick1 (The indented code is from Click2):

    Private Sub CommandButton1_Click()
    Dim LastRow As Long
    Worksheets("All").Range("A3:E5000").ClearContents
    Worksheets("All").Range("A1:A1").QueryTable.Refres h
    Range("F3:F3").Select

    ****LastRow = Application.CountA(ActiveSheet.Range("D"))
    ****Range("F3:G6500").ClearContents
    ****Range("F2:G2").Select
    ****Selection.AutoFill Destination:=Range("F2:G" & LastRow)
    ****Range("K6:K6").Select
    End Sub
    IT NOW WILL NOT RUN -- It stops at the "Selection.AutoFill" line with a nondescript error message. I think what is happening is that the query completes but it takes a few seconds for the data to appear in the WS & the error message appears before this happens. So, I tried inserting;
    For I= 1 to 100
    DoEvents
    Next i
    after the "Range("F3:F3").Select" but it did not help. SO,THIS IS MY 1st PROBLEM.

    Next, I tried moving the CommandButton3 code into CommandButton2 code as follows:

    Private Sub CommandButton2_Click()
    Dim LastRow As Long
    LastRow = Application.CountA(ActiveSheet.Range("D"))
    Range("F3:G6500").ClearContents
    Range("F2:G2").Select
    Selection.AutoFill Destination:=Range("F2:G" & LastRow)
    Range("K6:K6").Select
    ****'Private Sub CommandButton3_Click()
    ****Set pvtTable=Worksheets("Sheet1").Range("k6").PivotTab le
    ****pvtTable.RefreshTable
    ****Range("H1").Value = Now()
    End Sub

    It balks again, this time at the "pvtTable.RefreshTable" line with another nondescript error message.
    This is got me hog-tied. Any help will be appreciated.
    I reported similar problems in Post#: 43267.
    Regards and End Sub,
    Stephen
    <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combining code from cmdClicks into 1 macro

    Stephen,

    It is difficult to figure what your problem might be, without knowing what happens when your query table is refreshed. Try putting Dubug.Print LastRow in after each assignment of value to it, to see if takes on the expected value.

    What is the significance of Range("F3:F3").Select ?

    Would an upload of the workbook be feasible ?.

    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining code from cmdClicks into 1 macro

    Andrew:
    Range F3 is the starting point of the range I clear in order to fill down with the equations in F2:G2. I have attached the file. I had to reduce the size of the table returned by the query to make the size limit on attachments but it is rarely more than 500 rows anyway.
    I added 2 more buttons:
    Code for CommandButton4 shows how I tried to insert code from CommandButton2 into code for CommandButton1.
    Code for CommandButton5 shows how I tried to insert code from CommandButton3 into code for CommandButton2.
    Neither worked.
    Thanks for looking at this for me.
    Regards,
    Stephen
    Attached Files Attached Files

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combining code from cmdClicks into 1 macro

    Hello stephen,

    As I don't have the source data and am not that familiar with the Query tool, I remarked out the first clear contents statements (so that the data would remain), and ran the following code without getting an error.

    Private Sub CommandButton1_Click()
    Dim LastRow As Long
    'Worksheets("All").Range("A3:E5000").ClearContents
    Worksheets("All").Range("A1:A1").QueryTable.Refres h
    LastRow = Application.CountA(ActiveSheet.Range("D"))
    Debug.Print LastRow
    Range("F3:F3").Select
    Range("F3:G6500").ClearContents
    Range("F2:G2").Select
    Selection.AutoFill Destination:=Range("F2:G" & LastRow)
    Range("K6:K6").Select
    Set pvtTable = Worksheets(1).Range("k6").PivotTable
    pvtTable.RefreshTable
    Range("H1").Value = Now()
    End Sub

    Note Set pvtTable = Worksheets(1).Range("k6").PivotTable. The tab name for the sheet is All, but is referred to "Sheet1" in Private Sub CommandButton3_Click(). I used the Index number, but could have used "All". Would that have been a problem with your code ?.

    Hope it helps

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining code from cmdClicks into 1 macro

    Andrew:
    I was just about to post again. I had concluded 5 minutes ago that the clear contents thing was causing some problems, at least with the code that I was using as a workaround. What I do not get, though, is what happens with the
    "Worksheets("All").Range("A1:A1").QueryTable.Refre sh" code (I do not see that you commented it out)? It can't run because you do not have the connection to our database. Reason I ask is that I had concluded that XL goes on with the VBA code without waiting for the query to return the data to the WS. I pretty much know that for sure now. It may wait till the query is complete but not until the data are rerturned. So, if you do not clear the data first, the value for "LastRow" will be determined by the number of rows in the existing table (before the new qury completes). This is one of the reasons I got into this mess in the first place.
    I need something, like maybe the "Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)" thing to cue off but I do not know how to use it.
    If you would please take a look at the code below and let me know if you know of some way to do this.
    Private Sub CommandButton4_Click()
    Dim LastRow As Long
    'Worksheets("All").Range("A3:E5000").ClearContents
    Worksheets("All").Range("A1:A1").QueryTable.Refres h
    'newHour = Hour(Now())
    'newMinute = Minute(Now()) + 5
    'newSecond = Second(Now()) + 10
    'waitTime = TimeSerial(newHour, newMinute, newSecond)
    'Application.Wait waitTime
    Set currCell = Worksheets("All").Range("A2:A2")
    Do ' Outer loop
    Do While Not IsEmpty(currentCell) ' Inner loop.
    Set nextCell = currentCell.Offset(1, 0)
    If IsEmpty(nextCell) Then Exit Do
    Set currCell = nextCell
    Loop
    Loop Until Not IsEmpty(currentCell) ' Exit outer loop immediately.
    Worksheets("All").Range("R1:R1").Calculate
    'LastRow = Application.CountA(ActiveSheet.Range("D"))
    LastRow = ActiveSheet.Range("R1:R1")
    Range("F3:G6500").ClearContents
    Range("F2:G2").Select
    Selection.AutoFill Destination:=Range("F2:G" & LastRow)
    Range("K6:K6").Select
    Worksheets("All").UsedRange.Columns("F:G").Calcula te
    End Sub
    I tried the Do Loops first and then the Application.Wait waitTime but XL just jumps into the waittime before the data returns and 5-minutes goes by and it picks up where it left off, like nowhere.
    To use
    Application.OnTime Now + TimeValue("00:05:00"), "my_Procedure"
    does "my_Procedure" refer to another sub or can I use it in some way within the existing macro?
    Hope I have been clear enough; I rattled this off fast because I wanted to try and get this done before going home tonight.
    Thanks
    PS
    thanks for getting the PT refresh thing to work.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining code from cmdClicks into 1 macro

    Andrew:
    OK forget all that in the last post (or most of it anyway). Here is the essence of it: I think that the external query must be run as a separate thing and when it returns the data, then the user can go on to set off other macros.
    Here is what I did:
    Private Sub CommandButton4_Click()
    'Dim LastRow As Long
    'Worksheets("All").Range("A3:E5000").ClearContents
    Worksheets("All").Range("A1:A1").QueryTable.Refres h
    Application.OnTime Now + TimeValue("00:03:00"), "wait3andFillDown"
    End Sub

    Sub wait3andfillDown()
    Worksheets("All").Range("R1:R1").Calculate
    LastRow = ActiveSheet.Range("R1:R1")
    Range("F3:G6500").ClearContents
    Range("F2:G2").Select
    Selection.AutoFill Destination:=Range("F2:G" & LastRow)
    Range("K6:K6").Select
    Worksheets("All").UsedRange.Columns("F:G").Calcula te
    End Sub
    IT WENT RIGHT INTO THE Application.OnTime THING, TRANSFERED TO THE OTHER SUB AND IT ALL WAITED 3 MINUTES AND THEN STOPPED AT "Selection.AutoFill Destination:=Range("F2:G" & LastRow)" BECAUSE THE VALUE OF CELL R1 (which has the =counta(D) is 2 (because the sub cleared F3:G6500).
    So, what to do????
    What do you think?
    Have a nice weekend.
    Regards,
    Stephen

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combining code from cmdClicks into 1 macro

    Stephen, I snipped the following from VBA help, and wonder if it might be of some significance to your problem. If your query is SQL, it seems you might be able to hold control until the data has been returned.

    <font color=448800>expression.Refresh(BackgroundQuery)

    expression Required. An expression that returns a Chart, PivotCache, or QueryTable object. For the PivotCache object, the cache must have at least one PivotTable report associated with it.

    BackgroundQuery Optional Variant. Used only with query tables based on the results of an SQL query. True to return control to the procedure as soon as a database connection is made and the the query is submitted (the query is updated in the background). False to return control to the procedure only after all data has been fetched to the worksheet. If this argument isn

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining code from cmdClicks into 1 macro

    By God, I think you've got it. I saw this before but it never dawned on me what it was for. I was too emersed in trying different approaches. I am so convinced that this will work that I am going home without even trying it.
    Well, I have flushed another 5 hours down the <img src=/S/toilet.gif border=0 alt=toilet width=24 height=26> but I have something to look forward to on Monday.
    Thanks and <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>,
    Stephen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •