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.

Use an inputbox with opn 8

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


"simonc" wrote:

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.

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.
--
Gary''s Student - gsnu200793

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




simonc wrote:

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.

--

Dave Peterson