Input Box/User Form

[AdrianK]

Hello,

Bit of a theoretical question here, I have a macro already that creates stock reports from the base data for a number of clients, looping through splitting each client onto a seperate tab.

This is created from CSV file, with the macro itself being held in the user's personal workbook (so they can just pull out the data and run the macro).

They also use an input box during the macro to enter a user code so it saves to the correct location.

Is there a way to have an Input Box to come up with a drop down menu containing all the client names (data that is already held on one sheet) so they can select the client they want (I can then delete/not create all other sheets allowing them to see just that client's stock report).

Would this have to be a user form, and can a user form be created/populated through code, or does it already have to exist? If it does have to exist does it have to exist in a file (i.e. will I have to open the file with the form in it?)

I've never created a user form in VBA before, so am not sure.

Many thanks,

Adrian

[Bob Phillips]

It would be a userform. The form would have to exist, but the controls, such as the dropdown (a combo or listbox) can be populated upon load.

It would seem the personal.xls is the best place to hold the form.

The populating code would be (something) like this



With Worksheets("Clients") 'the sheet with the client names on

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Me.ListBox1.Clear
For i = 2 To LastRow 'this assumes row 1 is a header

Me.ListBox1.AddItem .Cells(i, "A").Value2
Next i
End With

[Simon Lloyd]

Maybe a little simpler with a combobox and combobox_change event?

download the attached files to your desktop, unzip it, go to the VBE and import it in to your personal.xls, make any changes needed, call the userform with ClientForm.Show

[AdrianK]

Thanks for that, i've been pulled away onto something else for a little while, but I will let you know how I get on as soon as possible.

Cheers,

Adrian