Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Generating a List (Excel 2000)

    I have a multipage on a userform. The user will be making a selection on Page1 and when he selects Page2 I would like to run a autofilter on a worksheet and list the result in a list box.

    I need help with what action will activate the procedure to do the vlookup?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Generating a List (Excel 2000)

    A MultiPage control has a Change event that fires when the user selects a different tab. You can check which tab has been clicked by inspecting the Value property of the MultiPage Control. This value is zero-based. i.e. the first page (tab) has Value = 0, the second page (tab) has Value = 1 etc.

    So you could have code like

    Private Sub MultiPage1_Change()
    If MultiPage1.Value = 1 Then
    ' Insert code to set AutoFilter etc.
    End If
    End Sub

    (Replace MultiPage1 by the name of your control)

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a List (Excel 2000)

    Thanks it works great

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a List (Excel 2000)

    I have a very strange thing now. When a user opens the file as Read/Write and another user opens it as Read Only then the Read Only user gets a Run-time error '1004': Select method of Worksheet class failed.

    I've attached the code. To recap. I have a userform that reads data from the "Original" worksheet. If the user select Page1 and sheet "Billing" get autofiltered and copies the filtered data to the Billingslist sheet. The listbox on Page1 then list the details on the Billinglist Please help


    Private Sub multpgContracts_Change()

    If multpgContracts.Value = 1 Then
    If SupplNo = "No SAP No" Then End
    combinedata
    Sheets("Billinglist").Select
    Me.lslBillingDetails.RowSource = Sheets("Billinglist").Range("A1:h30").Address
    Me.lslBillingDetails.ColumnWidths = "60;0;275;65;0;25;60;50"

    Else: Sheets("Billings").Range("C1").AutoFilter
    Sheets("Billinglist").Range("A1:H30").Clear
    Sheets("Original").Select

    End If

    End Sub

    Sub combinedata()
    Dim supplnos As String


    supplnos = "=" & SupplNo & "*"
    Sheets("Billings").Range("A1").AutoFilter Field:=3, Criteria1:=supplnos, Operator:=xlAnd
    Sheets("Billings").Range("A1").CurrentRegion.Copy
    Sheets("BillingList").Range("A1").PasteSpecial

    End Sub

Posting Permissions

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