Input box to allow user to select column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marcusdmc - 26 Sep 2007 13:47 GMT

I am trying to create a prompt that will allow the user to select the
column by asking them to click which month they wish to update.... The
names of the month are in Row 1 starting at column B. The
information for each month will be filled down each row for that
column. What are some ways to code this and then add an offset to
allow a copy/paste scheme I have created to be pasted in. The column
the user selects would be the Month.. i.e. B, C, D, E, F.... L, M.

so Range on mSheet in this instance would be C for February:

I would need the input box month selection to of mCol offset(2,0) to
drop 2 rows to begin the first name and then would offset(1,0) 4 times
and resave mCol till i reached the last row for that person

Then it would offset(3,0) to 3 rows when I reach a new name and then
resave that to the value of mCol

How would I replace mCol select to offset then resave mCol?

mSheet.Select
Range(mCol).Select
Sheets("Employees").Select
Range("C2").Select
Selection.Copy
mSheet.Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("D2").Select
Selection.Copy
mSheet.Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("E2").Select
Selection.Copy
mSheet.Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("F2").Select
Selection.Copy
mSheet.Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Reply to this Message

Tom Ogilvy - 26 Sep 2007 14:18 GMT

Dim rng as Range, r as Range
On error Resume Next
set rng = Application.InputBox("Select Month in row 1",type:=8)
On Error goto 0
if rng is nothing then
Msgbox "You hit cancel"
exit sub
End if

if rng.count > 1 or rng.row <> 1 or rng.column = 1 then
msgbox "Bad Selection"
exit sub
End if

set r = rng.offset(1,0).Resize(10,1)

. . .

You should not have data validation which uses the "Formula Is" form of data
validation as there is a bug associated with this and the
application.Inputbox function.

Signature

Regards,
Tom Ogilvy

> I am trying to create a prompt that will allow the user to select the
> column by asking them to click which month they wish to update.... The
[quoted text clipped - 49 lines]
> SkipBlanks _
> :=False, Transpose:=False

Reply to this Message

Marcusdmc - 26 Sep 2007 20:08 GMT

On Sep 26, 9:18 am, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:

> Dim rng as Range, r as Range
> On error Resume Next
[quoted text clipped - 77 lines]
>
> - Show quoted text -

Thank you! That went a long way towards getting the results I needed!

-Marcus

Reply to this Message