Dan Swartz
Board Regular
- Joined
- Apr 17, 2020
- Messages
- 53
- Office Version
- 365
- Platform
- Windows
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.
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.