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

Hi Jay,

I have tried a brand new spreadsheet. I have cut and pasted your code in.
Nothing has happened - I am not getting an error message at all, now but the
user name / date are not showing. I have then tried changing the detail from
ComboBox to DropDown, but no change.

I can see what your code is trying to do. It makes sense. It's very
frustrating. I suspect it's something really obvious which I don't
appreciate, because I am not used to VB, etc.

Thanks for trying!

"Jay" wrote:

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

Hi Lambs -

We'll get this back on track and the frustration will melt away...

It sounds like you added the control to the worksheet using the Forms
toolbar. Delete that dropdown control and add one using the Control Toolbox.
The Control Toolbox produces more modern controls that can be handled more
consistently. To add a combobox control from the Control Toolbox:

1. Turn on the Control ToolBox toolbar by right-clicking on any toolbar and
choosing 'Control Toolbox'.

2. Click on the Combobox button (in the toolbox) and draw your combobox on
the worksheet.

3. You'll see the new combobox appear with some circular "handles" along
its perimeter. That means you are in DesignMode and can edit the combobox
size and properties. Right-click the combobox and choose [Properties].

4. Find ListFillRange in the Properies window that opens and enter a
worksheet range that holds the list of options for the combobox, e.g.,
A10:A50.

5. At the top of the Properties window you'll see the 'Name' property.
Make a note of the name for later (it might be Combobox2, Combobox3, etc.
depending on how many you have constructed).

6. Close the properties window.

7. Double-click the combobox to open the worksheet module and copy the code
I provided there. Edit the name of the combobox in the VB code to match the
name noted in Step 5.

8. Switch back to the worksheet and turn off the Control DesignMode by
clicking the DesignMode icon in the Control Toolbox. Done.

------

Making an entry in the combobox should run its Change event. Good luck and
let me know what's next.

---
Jay