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.

 

[mikerickson]

Your form needs to know what cell is involved. If you change the calling code to

' in Worksheet_Change event

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

and your button codes to

' in userform code module

Private Sub OrangeCB_Click()
    Range(Me.Tag).Offset(0,1).Value = OrangeCB.Caption
    ColorPickerUF.Hide
End Sub

that should work.

 

[Dan Swartz]

Your form needs to know what cell is involved. If you change the calling code to

' in Worksheet_Change event

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

and your button codes to

' in userform code module

Private Sub OrangeCB_Click()
    Range(Me.Tag).Offset(0,1).Value = OrangeCB.Caption
    ColorPickerUF.Hide
End Sub

that should work.

I'll Give this a try! thanks. I'll let you know if that works.

 

[Dan Swartz]

I'll Give this a try! thanks. I'll let you know if that works.

Mike, this is worked perfectly! thank you! I wouldn't have got that! One More question. In all of this. I want to lock this to only work in a range of Cells. B2:B25.

I'm guessing it has to do with my Set Color Range code, but I could only get it to work this way.

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

 

[Fluff]

To limit the range you can use

Set ColorRange = Range("B2:B25")

 

[Dan Swartz]

To limit the range you can use

Set ColorRange = Range("B2:B25")

Tried that, but it makes the rest of the line an error.

 

[Fluff]

The rest of what line?

 

[Dan Swartz]

The rest of what line?

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

where do you put the range? No matter where I put it, it gives me an error. I'm assuming I have something wrong.

 

[Fluff]

Just replace that line with the code I suggested.

 

[Dan Swartz]

Just replace that line with the code I suggested.

LOL! Sometimes the simple things are just too simple! Thanks. that worked!