Pointers to appropriate functions

[Chris]

OK, I'm a semi-decent programmer with Excel/VBScript. I just have some
holes in my knowledge I need to fill.

Here's what I'm trying to do. I have a set of data in a spreadsheet. I
want to prompt the user for a column of data, take all of the data in that
column, one cell at a time, look that up in a database and populate another
column with the results.

I have this:
A B C D
101 Mickey Mantle Blue
102 Donald Duck Red
103 Homer Simpson Green
104 Bender Darobot Yellow


I want to prompt the user for the column containing the key (in the example,
A), insert a column between A and B and populate the newly inserted column
with data from the database.

The parts I can do revolve around the database. I can loop through all of
the data in A, look it up in a database and drop the results into a cell
beside it. No problem.

What I cannot do is:
a) Insert a column
b) (important) Request a column from the user

Sometimes the key will be in column A, sometimes AR. I need to prompt the
user for the column. I created a form and show it but cannot come up with
a good way to ask for the column. I have a text box and a spinner, not
elegant at all and certainly error prone.

I also have no idea how to insert a column immediately after the user's
requested column.

Pointers anyone?

Thanks!
Chris

 

[Bob Phillips]

a) Columns("B").Entirecolumn.insert

b) Use Application.Inputbox with a Type argument of 8. This allows the user
to select a range, and you can get the column from that input. Soimething
like

Set rng = application.InputBox("hello",type:=8)
myCol = rng.column

 

[Chris]

a) Columns("B").Entirecolumn.insert

b) Use Application.Inputbox with a Type argument of 8. This allows the
user to select a range, and you can get the column from that input.
Soimething like

Set rng = application.InputBox("hello",type:=8)
myCol = rng.column

I'll try it out and let you know. Very fast response, thanks!

Chris