Hello,

Programming is not my forte, so apologies for stating the obvious.

I want to code so that on selecting an option in a combo box, the cell to
the right of the combo box returns the current user's name, and then the date
the next cell along.

I can see from other posts that there is an 'on change' routine. I want a
few of these boxes, however, so I'd like the code to be relative to the
particular box, rather than an absolute reference, which I'd have to keep
re-jigging. Is there a way to 'cheat', for instance by working off the 'tied'
cell which returns 1,2,3, etc., depending on which option is selected?

Many thanks,

Hi Lambs -

One approach would be to use the TopLeftCell and OffSet properties as
follows (modify the column offset to suit):

Private Sub ComboBox1_Change()
With Me.ComboBox1.TopLeftCell
.Offset(0, 5) = Application.UserName
.Offset(0, 6) = Date
End With
End Sub
--
Jay


"Lambs" wrote:

Hello,

Programming is not my forte, so apologies for stating the obvious.

I want to code so that on selecting an option in a combo box, the cell to
the right of the combo box returns the current user's name, and then the date
the next cell along.

I can see from other posts that there is an 'on change' routine. I want a
few of these boxes, however, so I'd like the code to be relative to the
particular box, rather than an absolute reference, which I'd have to keep
re-jigging. Is there a way to 'cheat', for instance by working off the 'tied'
cell which returns 1,2,3, etc., depending on which option is selected?

Many thanks,

Hi Jay,

There's a problem: it doesn't like the 'Me' keyword.

"Invalid Use of Me Keyword"

Code is:

Private Sub DropDown2_Change()
With Me.DropDown2.TopLeftCell
.Offset(0, 5) = Application.UserName
.Offset(0, 6) = Date
End With

End Sub

I see you have ComboBox and I have DropDown; I have checked and it was a
ComboBox Form Control that I have added. Is this the cause of the problem?

Thanks,


"Jay" wrote:

Hi Lambs -

One approach would be to use the TopLeftCell and OffSet properties as
follows (modify the column offset to suit):

Private Sub ComboBox1_Change()
With Me.ComboBox1.TopLeftCell
.Offset(0, 5) = Application.UserName
.Offset(0, 6) = Date
End With
End Sub
--
Jay


"Lambs" wrote:

Hello,

Programming is not my forte, so apologies for stating the obvious.

I want to code so that on selecting an option in a combo box, the cell to
the right of the combo box returns the current user's name, and then the date
the next cell along.

I can see from other posts that there is an 'on change' routine. I want a
few of these boxes, however, so I'd like the code to be relative to the
particular box, rather than an absolute reference, which I'd have to keep
re-jigging. Is there a way to 'cheat', for instance by working off the 'tied'
cell which returns 1,2,3, etc., depending on which option is selected?

Many thanks,

Hi Lambs -

The 'Me' keyword shouldn't fail if the procedure resides in the worksheet
module of the worksheet that contains the combobox. However, you should be
able to delete the Me keyword because it is redundant.

--
Jay

"Lambs" wrote:

Hi Jay,

There's a problem: it doesn't like the 'Me' keyword.

"Invalid Use of Me Keyword"

Code is:

Private Sub DropDown2_Change()
With Me.DropDown2.TopLeftCell
.Offset(0, 5) = Application.UserName
.Offset(0, 6) = Date
End With

End Sub

I see you have ComboBox and I have DropDown; I have checked and it was a
ComboBox Form Control that I have added. Is this the cause of the problem?

Thanks,


"Jay" wrote:

Hi Lambs -

One approach would be to use the TopLeftCell and OffSet properties as
follows (modify the column offset to suit):

Private Sub ComboBox1_Change()
With Me.ComboBox1.TopLeftCell
.Offset(0, 5) = Application.UserName
.Offset(0, 6) = Date
End With
End Sub
--
Jay


"Lambs" wrote:

Hello,

Programming is not my forte, so apologies for stating the obvious.

I want to code so that on selecting an option in a combo box, the cell to
the right of the combo box returns the current user's name, and then the date
the next cell along.

I can see from other posts that there is an 'on change' routine. I want a
few of these boxes, however, so I'd like the code to be relative to the
particular box, rather than an absolute reference, which I'd have to keep
re-jigging. Is there a way to 'cheat', for instance by working off the 'tied'
cell which returns 1,2,3, etc., depending on which option is selected?

Many thanks,

Sorry Jay,

Every time I try to assign a macro to it, or to view the associated code, it
simply drops it into a module. I don't know the difference. Just cutting and
pasting the code into the relevant sheet doesn't seem to work either?

"Jay" wrote:

Hi Lambs -

The 'Me' keyword shouldn't fail if the procedure resides in the worksheet
module of the worksheet that contains the combobox. However, you should be
able to delete the Me keyword because it is redundant.

--
Jay

"Lambs" wrote:

Hi Jay,

There's a problem: it doesn't like the 'Me' keyword.

"Invalid Use of Me Keyword"

Code is:

Private Sub DropDown2_Change()
With Me.DropDown2.TopLeftCell
.Offset(0, 5) = Application.UserName
.Offset(0, 6) = Date
End With

End Sub

I see you have ComboBox and I have DropDown; I have checked and it was a
ComboBox Form Control that I have added. Is this the cause of the problem?

Thanks,


"Jay" wrote:

Hi Lambs -

One approach would be to use the TopLeftCell and OffSet properties as
follows (modify the column offset to suit):

Private Sub ComboBox1_Change()
With Me.ComboBox1.TopLeftCell
.Offset(0, 5) = Application.UserName
.Offset(0, 6) = Date
End With
End Sub
--
Jay


"Lambs" wrote:

Hello,

Programming is not my forte, so apologies for stating the obvious.

I want to code so that on selecting an option in a combo box, the cell to
the right of the combo box returns the current user's name, and then the date
the next cell along.

I can see from other posts that there is an 'on change' routine. I want a
few of these boxes, however, so I'd like the code to be relative to the
particular box, rather than an absolute reference, which I'd have to keep
re-jigging. Is there a way to 'cheat', for instance by working off the 'tied'
cell which returns 1,2,3, etc., depending on which option is selected?

Many thanks,

Hi Lambs -

Let's assume the name of the worksheet is "Lambs".

1. Press Alt+F11 to open the VB Editor.

2. In the Project Explorer pane on the left side of screen you should see
the Folder "Microsoft Excel Objects". Under that folder, you should see a
list of sheets in the workbook. Double-click on the "Lambs" sheet to open
its module (this is a worksheet module as opposed to a standard module listed
under the "Modules" folder. The purpose of worksheet modules is to hold
worksheet-specific code).

3. Paste the code (along with the 'Me' keyword) into the main window that
opened for the "Lambs" worksheet.

4. Switch back to the worksheet (Alt+F11) and test it out.

---
Jay