Consulting

Results 1 to 9 of 9

Thread: Solved: Creating a Userform

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

    Solved: 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:

    [vba]frm.Show[/vba]

  3. #3
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    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,938
    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


    [VBA]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[/VBA]

  7. #7
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    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,938
    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
  •