Automating a comment entry using code - specific requiremnents..

[Roger on Excel]

I have a spreadsheet for evaluating chemical processes.

The step numbers of the process descend in column A and the step's operation
adjacent to the step number in column B

I enter comments about various Operation steps in column R with the
appropriate step number in adjacent cell in column Q. At present I have to do
this manually which is tedious having to scroll over, enter the step number
and the comment. I also then make the operation cell yellow so I know that
step has a comment.

What I would like is to be able to click on one of the operation cells (in
column B) and then use a form button to cause the following to happen :

1) The step number of the operation is recorded (from the adjacent column A)
2) The step number entered into cell Q2
3) cell R2 selected so that I can enter a comment about the operation
4) a form button is pressed which returns the curser back to the operation
cell in column B and that cell is made yellow (or even better the comment
(from Q) is added to the comment dialog of that cell)
5) the cell below the origninally selected cell in column B is selected.
6) when I need to enter the next comment, it would return to the next free
cell in column Q and enter the appropriate step number and select the
adjacent free cell in column R for the next comment.
6) the return button takes me back to column B etc.. etc.

I know this sounds complicated, but can anyone help?

Best regards,

Roger Crump

 

[Per Jessen]

Hi Roger

I have used a inputbox rather than have the user enter directly into the
cell and pushing another button to finish the job.

Try this:

Sub EnterComment()
If ActiveCell.Column <> 2 Then Exit Sub

'Headings in row 1
If Range("Q2").Value = "" Then
TargetRow = 2
Else
TargetRow = Range("Q1").End(xlDown).Row + 1
End If

Range("Q" & TargetRow) = ActiveCell.Offset(0, -1).Value
Comm = InputBox("Enter your comment for " & vbLf & _
"step " & ActiveCell.Offset(0, -1).Value, "Input comment")
If Comm = "" Then Exit Sub
Range("R" & TargetRow) = Comm
ActiveCell.AddComment Comm
ActiveCell.Offset(1, 0).Select
End Sub

Regards,
Per

 

[Roger on Excel]

Per,

This is absolutely excellent

Works really great - i couldnt have asked for a better solution. Thankyou so
much!

All the best,

Roger

 

[Roger on Excel]

Per,

This code is great.

Is there a way to have a combobox in place of the input box? You see I have
a default list of comments i wish to use.

Let me know what you think?

Roger

 

[Per Jessen]

Thanks for your reply.

We can have the button call a userform with a combobox populated with the
default list of comments. Shall the user be able to add new comments?

Regards,
Per

 

[Roger on Excel]

Per,

It would not be nescessary to add new user comments to the list.

I have modified the code so that a combo box (pointing to the comments list)
is in the dialog. I did this by creating a new userform with command buttons
to activate the code and close the form. It looks as though it works fine as
I can now select the comment from the combo dropdown. However I would want to
retain the label code that you had as I quite like seeing it state "Step
1,2,3 etc" in the dialog.

How would I make the label in the new userform state the step number - i am
not sure how to do this in my newly created userform?

Best regards,

Roger

 

[Per Jessen]

Roger,

Add a Label to your userform and use this code:

UserForm1.Label1.Caption = "Select your comment for step " &
ActiveCell.Offset(0, -1).Value

Or if your want to display it as the caption of the userform:

UserForm1.Caption = "Select your comment for step " &
ActiveCell.Offset(0, -1).Value

Hopes this helps.

Best regards,
Per

 

[Roger on Excel]

Thanks Per,

These all work great - im learning alot about the coding for user forms.

All the best,

Roger