Interactive spreadsheet column select

[simonc]

My macro opens a spreadsheet selected by GetOpenFileName. The macro then
needs to know which column of the spreadsheet contains the data it needs. The
spreadsheets have no fixed format, so i would like the macro to open a
message box telling the user to click in the column of the spreadsheet which
has the data.

1) How can I code the macro to recognise the column from the user's mouse
click?
2) What sort of message box will still allow the spreadsheet to be accessible?

Grateful for help.

 

[Joel]

Use an inputbox with opn 8

cell = Application.InputBox("Select Cell", 8)
set mycell = Range(cell)

 

[Gary''s Student]

Sub simonc()
Set r = Nothing
Set r = Application.InputBox(prompt:="pick a column", Type:=8)
MsgBox (r.Column)
End Sub

Using type #8 allows the user to pick ranges either with the mouse or the
keyboard.

 

[Dave Peterson]

After the workbook is opened, you can use:

Dim myCol as range
'a bunch of code to open the workbook


set myCol = nothing
on error resume next
set mycol = application.inputbox _
(Prompt:="Select a cell to determine the column", type:=8)
on error goto 0

if mycol is nothing then
'user hit cancel, what should happen?
else
set mycol = mycol.cells(1).entirecolumn
'just to show that it worked
msgbox mycol.address & vblf & mycol.column
end if