Consulting

Results 1 to 9 of 9

Thread: Creating a Userform

  1. #1
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location

    Creating a Userform

    I was trying to attache the form called "frm" I created to the standard macro. But I don't know how...


    The idea is to make the form pop up, choose the right scenarios in it, and run standard macro in "Data Selection" tab. However I can't. it (form)exist independently. I know I have to identify it somehow for the macro to recognize it. I also can't get any values from "frm" to attach to the spreadsheet and to function properly.



    The spreadsheet is attached..

    I'm sure bits of the code are incorrect....

    Pleeeeese help

    Thanks in advance.

  2. #2
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Whatever your form's called:

    frm.Show

  3. #3
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    I don't see anything in the DataSelection tab? Is there supposed to be something in there besides 'Instructions'? Are you just wanting to call your form, or change data from it?

  4. #4
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location
    I can't make my OK button to close after choosing scenarios.

    The code is in the tab called "Data Selection".

    Besides, After I click OK, how do I proceed?

    Now the form shows up, but I can't make it to populate the data from the form into the right cells in the tab "NON II" cell f6 for the list box, and fist scenario in f9 (combobox1), second in ae9(combobox2). I can't get it to populate in the cells. The ida is: a)the form pops-up b) I pick from the lists of 3 boxes (org, scenario), c)then retrieve data from the system, and run loop, if I picked more than one org in the list box?..

    Thanks in advance for all your help.


  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    So why don't you have the button on DataSelection call up the userform "frm" and put the code from that button in your userform "Ok" button? Or is that what you're saying?

  6. #6
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location
    It seems I fixed most of it. But I can't close the Project. "Block If without End IF" ???
    I'm not sure what I need to do


    Private Sub OK_Click()
    Dim i As Variant
    Dim Chosen As Integer
    Dim CurList As ListBox
    Dim UserForm As frm
    Dim LTemp As Variant, LItem As Variant
    Dim ActiveSheet As Variant
    For i = 0 To ListBox1.ListCount - 1
        Sheets("Data Selection").Select
        If ListBox1.Selected(i) Then
            Worksheets("NON II").Range("F9") = ComboBox1.Text
            Worksheets("NON II").Range("AE9") = ComboBox2.Text
            'If exit is chosen, exit the procedure.
            If Chosen = 0 Then GoTo canceled
                frm.Hide
                ' Set the variable LTemp equal to the array of values
                ' indicating the selection state of the list box items.
                LTemp = CurList.Selected
                ' Iterate through the loop once for each item in the Organization List box
                'LTemp holds the selected organizations
                For Each LItem In LTemp
                    ' If the value of the current item is True...
                    If LItem = True Then
                        ' CurList.List(i) changes org of selected item in report
                        Worksheets("NON II").Range("F6") = ListBox1.List(i)
                        'Select report and login
                        Sheets("NON II").Select
                        Sheets("NON II") = ActiveSheet.Name
                        esb_Login (CurrentSheet)
                        'RetrieveNON_II Range on NONII tab
                        Sheets("Non II").Select
                        esb_Retrieve9
                        'RetrieveExp Range on Exp tab
                        Sheets("Exp").Select
                        esb_Retrieve1
                        'RetrieveBS Range on BS tab
                        Sheets("BS").Select
                        esb_Retrieve2
                        'RetrieveSpread Range on Spread tab
                        Sheets("Spread").Select
                        esb_Retrieve3
                        'RetrieveInterestIncome Range on InterestIncome tab
                        Sheets("Int_Income").Select
                        esb_Retrieve4
                    End If
                    i = i - 1
                Next
                ' If the Cancel button was chosen, exit procedure.
                canceled:
                Sheets("NON II").Select
    End Sub

  7. #7
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    After your Next, put an End If. You have two If's but only one End If. Your first one is prior to your For Each loop.

  8. #8
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location
    Thanks!!!! So much!

  9. #9
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    No problem. Glad you got it working.

    Take care!

Posting Permissions

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