PDA

View Full Version : A Combo Box for Certain Types of Information from another worksheet??



Incognitus
10-13-2012, 05:02 AM
Hello there,

I had an idea for something that would really improve efficiency on a set of spreadsheets used by multiple users, however I can only work out how to do it in an extremely inefficient way - and I'm sure there must be something more quick and effective!

In a nutshell - Worksheet B contains rows of data - and chances are when it came to entering the data, the information for column A,B,C was available, but D,E,F weren't.

I've already added a form to Worksheet A which allows entry of A,B,C directly into worksheet B - which is saving us lots of time, however I wanted to a new form which would contain a listbox and several data entry/option buttons to allow an easier update of the outstanding information.

Ergo, a listbox containing all Column A values from Worksheet B where D is blank, and then allow entry of D,E,F and update with the click of a button on the bottom.

At the moment, I'm filtering for all blanks on column D, copying the entire row into a hidden worksheet on worksheet A, and then using that hidden worksheet to feed the values to my form. Once something has been input, I then copy the entire row straight back to worksheet B whilst simultaneously deleting the pre-existing one. Update, rinse, repeat.

^ Can someone advise me of the more efficient way to be doing this?

snb
10-13-2012, 07:57 AM
This was waiting for you all the time.

http://www.snb-vba.eu/VBA_Userform_in_database_en.html

Incognitus
10-13-2012, 12:22 PM
This was waiting for you all the time.

http://www.snb-vba.eu/VBA_Userform_in_database_en.html

So if I change all the references to worksheet "Data" to be the other workbook/worksheet.... and how can I limit the database to just a range where D is missing values??

Incognitus
10-14-2012, 05:05 AM
Right, I've got the form working how I want it to, however, as I said because this is in another workbook, I'm currently copy/pasting from one book to another and offsetting so it is the last thing added to that worksheet.

How can I amend this to find the existing record and paste over it?

snb
10-14-2012, 07:59 AM
You can use any workbook:


private Sub userform_initialize()
with getobject("G:\OF\anotherworkbook.xlsx")
combobox1.list=.sheets(1).cells(1).currentregion.value
.close false
end with
end sub


You can write all changed data using

Private Sub commandbuttonclose_click()
with getobject("G:\OF\anotherworkbook.xlsx")
.sheets(1).cells(1).resize(ubound(combobox1.list)+1,ubound(combobox1.list,2 ))= combobox1.list
.close True
end with
end sub