UserForm Button Data entry from multiple Choices

[Dan Swartz]

NEED:
I need the ability that when an Item number is entered into a range of cells, "B2:B25", that immediatly a message box pops up where the operator MUST choose and color to continue. When selecting the color, it will enter that value in the adjacent cell. For example. if I enter the item number for Flooring in B5, the "Select Color" Userform pops up and if I select "Green", it will enter "Green" in C5.

I will use formulas to pull all other values. just need it to enter the chosen color in column C.

I have the formula and UseForm working......Kinda.

In this example, I just entered 001975 in B5 and the "Select Color" user form pops up asking for the color code. if I click any color, it should enter the color in C5.

My problem: This will work as long as I start at B2 and work row by row downward. However, if I start at B15 or so and work up, the results are completly wrong. It puts the value in Column C in very random spots.

The Color Picker also needs locked to a range "B2:B25". anything outside of B2:B25, I don't want it to pop up.

the code is close. but I can't seem to lock the range and make it work in any direction.

This is my code to call the UserForm

Option Explicit
__________________________________________________________________________
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColorRange As Range

Set ColorRange = Range(Cells(2, "B"), Cells(Rows.Count, "B").End(xlUp))

If Target.Cells.Count > 1 Then Exit Sub

If Not Application.Intersect(Target, ColorRange) Is Nothing Then
ColorPickerUF.Show
End If

End Sub
____________________________________________________________________________

This is my code for each Color Button.

Private Sub BlueCB_Click()
With ActiveSheet
.Cells(Rows.Count, "b").End(xlUp).Offset(0, 1).Value = BlueCB.Caption
ColorPickerUF.Hide
End With
End Sub

Private Sub GreenCB_Click()
With ActiveSheet
.Cells(Rows.Count, "b").End(xlUp).Offset(0, 1).Value = GreenCB.Caption
ColorPickerUF.Hide
End With
End Sub


Private Sub YellowCB_Click()
With ActiveSheet
.Cells(Rows.Count, "b").End(xlUp).Offset(0, 1).Value = YellowCB.Caption
ColorPickerUF.Hide
End With
End Sub
Private Sub RedCB_Click()
With ActiveSheet
.Cells(Rows.Count, "b").End(xlUp).Offset(0, 1).Value = RedCB.Caption
ColorPickerUF.Hide
End With
End Sub
Private Sub PurpleCB_Click()
With ActiveSheet
.Cells(Rows.Count, "B").End(xlUp).Offset(0, 1).Value = PurpleCB.Caption
ColorPickerUF.Hide
End With
End Sub

Private Sub OrangeCB_Click()
With ActiveSheet
.Cells(Rows.Count, "b").End(xlUp).Offset(0, 1).Value = OrangeCB.Caption
ColorPickerUF.Hide
End With
End Sub

I would upload a sample sheet, but can't figure out how too.

 

[Fluff]

Glad we could help & thanks for the feedback.