What does "select another empty excel file" mean?

I guessed that creating a new workbook with a single worksheet would be ok.

That's what this did:

set newwks = workbooks.add(1).worksheets(1)
wksCreated = true
end if



sam wrote:

Hi Dave,

I am trying to get this thing to work but its not working as I want.

Lets say I have a button "Export" on the userform, This userform has a
dropdown menu, which has student names, and selecting a student name from
that dropdown would populate other fields on the form such as, Name, Age,
Task etc..
So if i select Student1 from the dropdown, I will get the data of Student1
displayed on the form, If i select Student2 from the dropdown, I will get
data of Student2 displayed on the form.
Now when I click export, I want it to:
- open a file dialog box that lets me select another empty excel file
- once i select the excel file, i want the details of the select student to
be displayed in Row1
-If i select another student from the dropdown, i want the details of that
student to be displayed in Row2... and so on..
- Once I am done selecting the students, I want to manualy save the excel
file where I exported the student data.

Is this possible?

Thanks in Advance

"Dave Peterson" wrote:

Set up the output sheet first:

'at the top of the userform module
dim WksCreated as boolean

sub commandbutton1_click()

dim newWks as worksheet
dim DestCell as range

if wkscreated = false then

'new workbook with a single sheet
set newwks = workbooks.add(1).worksheets(1)
wksCreated = true
end if

with newwks
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

with destcell
.value = me.textbox1.value
.offset(0,1).value = "next column over"
.offset(0,5).value = "5 columns to the right"
end with

End sub

Untested, uncompiled.



Then do the


sam wrote:

Hey Dave, Rite now with this code it is saving seperate copies on clicking
"Insert"

I want to loop it through the same worksheet and save the data on the same
sheet but new row every time I hit "Insert"

SO when I click "Insert" after selecting a Task from the dropdown... I want
that Task and ROll no to populate in Row1, then If I input a different roll
no and select a Task for that and click "Insert" I want this to populate in
Row2 and so on.. Untill I manually save the workbook

Thanks in advance

"Dave Peterson" wrote:

dim newWks as worksheet

'new workbook with a single sheet
set newwks = workbooks.add(1).worksheets(1)

with newwks
.range("A1").value = me.textbox1.value 'roll_no
.range("B1").value = me.whatever.....
End with

with newwks.parent 'the new workbook
.saveas _
filename:="C:\newfilename_" & format(now,"yyyymmdd_hhmmss") & ".xls", _
fileformat:=xlworkbooknormal
.close savechanges:=false
end with



sam wrote:

Hey Dave, Thanks for this.. It worked out great!

I am trying to create a button €ŀœInsert€ÂÂ� in the same form.

What I want this button to do is:
When I click Insert.. It Opens a new workbook, Inserts the current student
details in first row(Roll_No, Task, ect.), SO basically Every time I hit this
€ŀœInsert€ÂÂ� button I want to populate current student detail(Roll_No, Task,
ect.) in a new row.

Is this possible?

Thanks in advance

"Dave Peterson" wrote:

After this section of your code:

me.combobox1.clear

for each mycell in myrng.cells
if mycell.value = clng(me.textbox1.value) then
me.combobox1.additem mycell.offset(0,-1).value
end if
next mycell

if me.combobox1.listcount = 0 then
'nothing found
me.combobox1.enabled = false
else
me.combobox1.enabled = true
end if

===================================
You could even check beforehand:

With worksheets("Sheet2")
set myrng = .range("B2",.cells(.rows.count,"B").end(xlup))
end with

me.combobox1.clear 'no matter what

if application.countif(myrng, me.textbox1.value) = 0 then
me.combobox1.enabled = false
else
me.combobox1.enabled = true 'just in case it was ever false
for each mycell in myrng.cells
if mycell.value = clng(me.textbox1.value) then
me.combobox1.additem mycell.offset(0,-1).value
end if
next mycell
end if

sam wrote:

Hey Dave, Thanks a lot for all you help in getting this to work.

I was working on this and I came across an issue where students input values
in "Roll_No" field that are not present in the sheet. Is there a way where I
can disable the "Tasks" dropdown if they input an invalid "Roll_No" or a
"Roll_No" that is not present in the sheet? So basically enable the "Tasks"
dropdown on form only if the "Roll_No" they input is from the list in the
sheet.

Thanks in advance

"Dave Peterson" wrote:

You're not really filtering the worksheet based on the roll number, right?

If you're not...

You can loop through the range.

Dim myRng as range
dim myCell as range

With worksheets("Sheet2")
set myrng = .range("B2",.cells(.rows.count,"B").end(xlup))
end with

me.combobox1.clear

for each mycell in myrng.cells
if mycell.value = clng(me.textbox1.value) then
me.combobox1.additem mycell.offset(0,-1).value
end if
next mycell




sam wrote:

Hey Dave, to make it clear. Here is and eg of how the data looks on sheet

Tasks Roll_No
3 11
5 11
4 12
6 12
2 13
5 13
3 14
4 14
1 15
2 15
1 16
4 16

So lets say you select "12" from Roll_No column filter
then it will display

Tasks Roll_No(filter)
4 12
6 12

So I want this 4 and 6 to be displayed in the dropdown list on userform too
Is there a way to capture filter values(4 and 6) from a sheet to a userfrom.
So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown
on the userform.

Hope I made it clear

"Dave Peterson" wrote:

Once you find the cell that holds the name, you can use VBA's split command (if
you're using xl2k or higher) to parse that data into an array.

Then use that array as the combobox's .list.

Dim myStr As String
Dim myArr As Variant

myStr = "1,2,3" 'how ever you find that cell would go here

myArr = Split(myStr, ",")

With Me.ComboBox1
.Clear
.List = myArr
End With

sam wrote:

Thanks a lot for you help Dave,
One more thing I wanted in my form was a way to capture filter values from
sheet2 into a Dropdown list in the user form. Is there a way to do this?

For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and
each student can have multiple "Tasks" such as..

John: 1,3
Jill: 1,5
jack: 3,5,6
Bill: 2,4,6
and so on... I have this column set as a filter(Behaves like a dropdown on
excel sheet), so John will have a Dropdown Filter with values 1 and 3 in
"Tasks" column

So basically, I want these filter values to be displayed on the form as
dropdown menu with 1 and 3 displayed for John.

Hope I made it clear

Thanks in advance

"Dave Peterson" wrote:

Dim res as variant

with worksheets("sheet2") 'I'd change the name to something meaningful
'look for a match using a string
res = application.match(me.textbox1.value,.range("A:A"), 0)
if iserror(res) then
'look for a match using a real number
res = application.match(clng(me.textbox1.value),.range(" A:a"),0)
end if

if iserror(res) then
'no match between textbox1 and column A of Sheet2
'what should happen
beep
msgbox "Invalid entry"
exit sub '???
end if

me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b
me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F
End with

(Untested, uncompiled. Watch for typos.)


sam wrote:

Hi All,

How can I use Inded and Match function on VBA to populate certain form fields?

For eg: I have a userform where Users input:

Roll No(Unique to every student):
Last Name :
First Name :
Subject1 :
Ph No:

What I want to do is: Once Users Input Their Roll No, I want to populate
their Last Name, First Name, Subject1 and Ph No. Also the roll number might
not be in descending order, Hence I want to use somethign like Index and
Match function, So once a student Inputs the roll number, It will match the
roll number and populate other fields respectively

The button to launch the Form is on Sheet1 and All the student data is in
Sheet2.

Hope I made it clear

Thanks in Advance

--

Dave Peterson



--

Dave Peterson