| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating a comment entry using code - specific requiremnents..
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Automating a comment entry using code - specific requiremnents..
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" skrev i meddelelsen ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Automating a comment entry using code - specific requiremnents
Per,
This is absolutely excellent Works really great - i couldnt have asked for a better solution. Thankyou so much! All the best, Roger "Per Jessen" wrote: 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" skrev i meddelelsen ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Automating a comment entry using code - specific requiremnents
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" wrote: 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" skrev i meddelelsen ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Automating a comment entry using code - specific requiremnents
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" skrev i meddelelsen ... 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" wrote: 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" skrev i meddelelsen ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Automating a comment entry using code - specific requiremnents
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" wrote: 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" skrev i meddelelsen ... 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" wrote: 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" skrev i meddelelsen ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Automating a comment entry using code - specific requiremnents
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" skrev i meddelelsen ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automating Data Entry (Position the cursor, insert row) | Excel Programming | |||
| Automating entry in a cell | Excel Worksheet Functions | |||
| Visual Basics - Automating Specific Sheet Names | Excel Discussion (Misc queries) | |||
| Auto date or comment entry | Excel Programming | |||
| Automating entry using validation | Excel Programming | |||
Hybrid Mode
