PDA

View Full Version : Solved: Creating a Userform



Nova
08-23-2004, 08:17 AM
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.

SJ McAbney
08-23-2004, 11:42 AM
Whatever your form's called:

frm.Show

Zack Barresse
08-23-2004, 12:04 PM
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?

Nova
08-24-2004, 06:19 AM
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.

Zack Barresse
08-24-2004, 10:54 AM
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?

Nova
08-24-2004, 11:15 AM
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

Zack Barresse
08-24-2004, 11:31 AM
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.

Nova
08-24-2004, 12:07 PM
Thanks!!!! So much!

Zack Barresse
08-24-2004, 02:07 PM
No problem. Glad you got it working. :)

Take care!