Results 1 to 7 of 7
  1. #1
    ralph_davies
    Guest

    Opening WBks with Macros

    I have now isolated what appears to be the real problem in my application. see Posting 33812, previous problem was a ferfy, I solved that by using a third workbook containing the open statement. Then using Application.Run to set it going. However this did not solve the missing data problem.

    When I open a file which has an auto_open macro in it.
    The behaviour of the auto_open macro differs depending on how the file is opened.
    If I open it from file open everything works perfectly.
    If I open it via another macro I loose some data.

    Specifically references to some data in dropdowns on a user form don't work.
    The dropdown lists appear to work but when an Item is selected data which is associated with the selection is not entered/changed on the userform. This works when I open the file by any other means.

    Have I found a New Excel 97 Bug or is there a better way to open from within a macro.

  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: Opening WBks with Macros

    Have you tried the Workbook_Open() event as opposed to Auto_Open. It is the preferred method and may avoid the problem you are having. The code is placed in the ThisWorkbook object and not in a general module. It might be worth a try.

    Andrew C

  3. #3
    ralph_davies
    Guest

    Re: Opening WBks with Macros

    That is how I have done it.

    Sorry I should have included the Code as below.

    Private Sub OpenQuote1()
    Workbooks.Open ("C:Quote1.xls")
    End Sub

    This is the Auto_open Code

    Private Sub Workbook_Open()
    Application.WindowState = xlMaximized
    If ThisWorkbook.Name = "Quote1.xls" Then
    Application.Run "Quote1.xls!GetData" 'gets data from the Quote and puts it on the user form
    Application.Run "Quote1.xls!showForm"
    Else
    EditForm.Show 'used to re-name to Quote1 if it is an old quote then open the workbook this is how it all starts.
    End If
    End Sub


    Here is the code attached to the Form which changes the entries when a new selection is made in a pulldown.

    Everything works fine when opened via File Open.

    The Find returns nothing even though the item is in the list when the macro opens the WBk ?????????????????????



    Private Sub CName_Click()
    Call UserForm_Initialize
    CustomerName = QuoteForm.CName.Text
    Sheets("Data").Activate
    If CustomerName <> "" Then
    With Sheets("Data").Range("Customer_Name")
    Set result = .Find(CustomerName, LookAt:=xlWhole, MatchCase:=True, LookIn:=xlValues)
    If result Is Nothing Then
    Exit Sub
    Else
    result.Cells.Select
    RowNumber = Selection.Row - 1
    PhoneNumber = Range("Phone").Item(RowNumber).Value
    FAXNumber = Range("FAX").Item(RowNumber).Value
    E_MailAddress = Range("E_Mail").Item(RowNumber).Value

    QuoteForm.Phone.Value = PhoneNumber
    QuoteForm.FAX.Value = FAXNumber
    QuoteForm.E_Mail.Value = E_MailAddress

    End If
    End With
    End If
    End Sub


    This is the code that sets up the pull down lists data

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening WBks with Macros

    Could you cycle through the cells manually instead of using a "selection.Find"?

    It might be slower- but you could then at least step through the code and isolate the problem a little more.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening WBks with Macros

    Off-thread here, sorry- but for the non-Australians-
    <hr>previous problem was a ferfy<hr>

    Actual spelling furphy. Means a rumour or something misleading. It originated in WW1 when the troops fighting in Gallipoli, Turkey, had water tanks made by "Furphy and Sons". The people carrying the water also carried the rumours.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening WBks with Macros

    Ralph,

    Sorry my previous reply was short and not too informative. I was on my way out of work.

    Here's a way of cycling through, for instance, column 2 of a named range called "test":

    <pre>Dim rngData As Range
    Dim i As Integer
    Set rngData = Range("test")

    For i = 1 To rngData.Rows.Count
    Debug.Print rngData(i, 2)
    Next
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    ralph_davies
    Guest

    Re: Opening WBks with Macros

    Geoff, Thanks for the advice and code.

    I have tried Your suggestion and in the process discovered that the On_Click and the On_Change Events for comboboxes in userForms do not work when a WBk is opened via a macro. I think this may be an Excel 97 Bug ??????

    I have solved my original problem with a work around to avoid opening via the macro. ie. By re-naming my file via SaveAs to Quote1.xls as the first line of my Auto_Open Macro.
    I can then reference it from the Price List.
    This avoids having to SaveCopyAs then open it using the macro so the file looked like it was created with the Template Quote.xlt ie Quote1. (with no extension)

    I am still interested to know if the non-execution of some userForm events when a file is opened via a macro is a known problem and is there a work around for it.

    Thanks again

    Ralph

Posting Permissions

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