Newbie : Combo box on cell ?
 

I am using Excel 97.

I have the following task. Management wants to control what the end user
puts into a column of the spreadsheet. The column is a comment column. The
end user can only enter 1 of 5 sentences. For example, the combo box might
have a choice
like, "The client was charged ________ ". Then the user types in the number
in the underline.

Can the above be done. Is there another way. The managers prefer a combo box
on each cell.

Newbie : Combo box on cell ?
 

Newbie,

Consider using "Data Validation" with the selection items
in a List. See Excel help for this feature.

Dave

-----Original Message-----

I am using Excel 97.

I have the following task. Management wants to control
what the end user
puts into a column of the spreadsheet. The column is a
comment column. The
end user can only enter 1 of 5 sentences. For example,
the combo box might
have a choice
like, "The client was charged ________ ". Then the user
types in the number
in the underline.

Can the above be done. Is there another way. The managers
prefer a combo box
on each cell.



.

Newbie : Combo box on cell ?
 

If you are familiar macroes you could do as follows:

1) Go in to the macro-editor
2) insert a userform (in the code named userform1)
3) put a combobox on the userform (in the code named combobox1)
4) dbl-click on the userform (enter the code-editer)
5) insert the following :

Private Sub ComboBox1_Change()
ActiveCell = ComboBox1.Text
UserForm1.Hide
End Sub

Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell = ComboBox1.Text
UserForm1.Hide
End Sub

Private Sub UserForm_Initialize()
ComboBox1.Clear
ComboBox1.AddItem ("The client was charged ")
ComboBox1.AddItem ("I'm a good boy ")
ComboBox1.AddItem ("It wasn't me")
ComboBox1.AddItem ("Give me an icecream ")
ComboBox1.AddItem ("Whatever..... ")
End Sub

6) in the objectbrowser you select sheet 1
7) In the edit feild you insert :

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As Boolean)
If Target.Column = 5 Then
UserForm1.Show ' Replace "5" with the columnnumber of the comment
column
Cancel = True ' prevents the normal Rightclick rutine from running
end if
End Sub


This should do it!

When you rightclick on the "comment-column" the userform shows and
when you make a selection it puts the selection into the active cell.

Hope you can use it !

;-) Søren Remfeldt

"RM" wrote in message link.net...
I am using Excel 97.

I have the following task. Management wants to control what the end user
puts into a column of the spreadsheet. The column is a comment column. The
end user can only enter 1 of 5 sentences. For example, the combo box might
have a choice
like, "The client was charged ________ ". Then the user types in the number
in the underline.

Can the above be done. Is there another way. The managers prefer a combo box
on each cell.