LordDragon
12-31-2015, 03:51 AM
Greetings,
I have created the following user form so the users can edit the parts, or add new ones.
15072
The text field part works great, and the radio buttons call their proceedures fine.
The problem lies in making the radio button proceedures actually do something.
What I want them to do is:
1. Set the color of the cells to match the color of the other parts with the same importance.
2. Put a number (1 - 4) in Column S that matches the importance (To help with sorting later).
a. So if the part is Optional, the number should be 4.
3. Place a border around the cells.
I have the following code that I use when I'm editing the entire sheet. I'm working on modifying it to work on this. The main issue is getting it to work on the current selection row only.
Function ShadeOptional()
'Shades every other row of optional parts light blue and the alternate rows pale blue.
'Declare the variables
Dim lngRow As Long
Dim lngCol As Long
With ActiveWorkbook.Worksheets("Sheet1")
For lngCol = 1 To 5
For lngRow = 16 To 20
Cells(lngRow, lngCol).Interior.Color = RGB(204, 255, 255)
Next lngRow
Next lngCol
End With
End Function
The way I currently use this is I have a function for each color (Importance). I would manually edit the code and put the sheet name I'm editing in and adjust the row ranges (The column ranges never change). Then, since not every sheet has all the colors, I would simply commnet out the code that calls the function if that color is not used.
I have the following code in the same sub that I use to set the borders. It would also get commented out if it wasn't needed. If it is needed, I would manually change the range before running the sub.
Function ApplyBorders()
'Applies the borders to the cells on the sheet.
Range("A2:E20").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End Function
In case you need it, here is the form code.
Option Explicit
'Declare Module wide variables.
'These can be used in any routine in this UserForm.
Private nSheets As Variant 'Sheets to ignore.
Private lngRow As Long
Private lngCol As Long
Private lngSet As Long
Private Sub UserForm_Initialize()
'Initialize Module wide Variables
nSheets = Array("Instructions", "Rig Survey Form", "System Selection", "Order Summary", "RMS Order", "Master DataList", "Master Parts List", "RSFImport")
End Sub
Private Sub btnCancel_Click()
Unload frmEdit
End Sub
Private Sub btnEdit_Click()
'Provides a method to add new parts.
'Provides a method to change the priority of the new or existing parts.
'Make sure they are using a system page.
For lngSet = LBound(nSheets) To UBound(nSheets)
If ActiveSheet.Name = nSheets(lngSet) Then Exit Sub
Next lngSet
'Make sure something is entered in the text box
If txtEdit.Text = "" Then
MsgBox "You must enter a Part Number."
Exit Sub
End If
With ActiveSheet
'Make sure they are in the Part Number column
If Intersect(ActiveCell, .Columns(3)) Is Nothing Then Exit Sub
'Put the Part Number in the field.
ActiveCell.Value = txtEdit.Text
'Set the importance of the part.
If Me.Controls("optRequired") Then PartRequired
If Me.Controls("optChoice") Then PartChoice
If Me.Controls("optRecommended") Then PartRecommended
If Me.Controls("optOptional") Then PartOptional
End With
'Clear the controls
txtEdit.Text = ""
optRequired = True
txtEdit.SetFocus
Unload frmEdit
End Sub
Private Sub PartRequired()
'Sets the part importance to required by coloring the cells appropriately and setting the importance number in Column S.
MsgBox "This part is Required."
End Sub
Private Sub PartChoice()
'Sets the part importance to required by coloring the cells appropriately and setting the importance number in Column S.
MsgBox "This part is Required, but there is a choice to make."
End Sub
Private Sub PartRecommended()
'Sets the part importance to required by coloring the cells appropriately and setting the importance number in Column S.
MsgBox "This part is Recommended."
End Sub
Private Sub PartOptional()
'Sets the part importance to required by coloring the cells appropriately and setting the importance number in Column S.
MsgBox "This part is Optional."
End Sub
As always, I appreciate any help I can get. If I solve my problem myself, I will post my solution here.
I have created the following user form so the users can edit the parts, or add new ones.
15072
The text field part works great, and the radio buttons call their proceedures fine.
The problem lies in making the radio button proceedures actually do something.
What I want them to do is:
1. Set the color of the cells to match the color of the other parts with the same importance.
2. Put a number (1 - 4) in Column S that matches the importance (To help with sorting later).
a. So if the part is Optional, the number should be 4.
3. Place a border around the cells.
I have the following code that I use when I'm editing the entire sheet. I'm working on modifying it to work on this. The main issue is getting it to work on the current selection row only.
Function ShadeOptional()
'Shades every other row of optional parts light blue and the alternate rows pale blue.
'Declare the variables
Dim lngRow As Long
Dim lngCol As Long
With ActiveWorkbook.Worksheets("Sheet1")
For lngCol = 1 To 5
For lngRow = 16 To 20
Cells(lngRow, lngCol).Interior.Color = RGB(204, 255, 255)
Next lngRow
Next lngCol
End With
End Function
The way I currently use this is I have a function for each color (Importance). I would manually edit the code and put the sheet name I'm editing in and adjust the row ranges (The column ranges never change). Then, since not every sheet has all the colors, I would simply commnet out the code that calls the function if that color is not used.
I have the following code in the same sub that I use to set the borders. It would also get commented out if it wasn't needed. If it is needed, I would manually change the range before running the sub.
Function ApplyBorders()
'Applies the borders to the cells on the sheet.
Range("A2:E20").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End Function
In case you need it, here is the form code.
Option Explicit
'Declare Module wide variables.
'These can be used in any routine in this UserForm.
Private nSheets As Variant 'Sheets to ignore.
Private lngRow As Long
Private lngCol As Long
Private lngSet As Long
Private Sub UserForm_Initialize()
'Initialize Module wide Variables
nSheets = Array("Instructions", "Rig Survey Form", "System Selection", "Order Summary", "RMS Order", "Master DataList", "Master Parts List", "RSFImport")
End Sub
Private Sub btnCancel_Click()
Unload frmEdit
End Sub
Private Sub btnEdit_Click()
'Provides a method to add new parts.
'Provides a method to change the priority of the new or existing parts.
'Make sure they are using a system page.
For lngSet = LBound(nSheets) To UBound(nSheets)
If ActiveSheet.Name = nSheets(lngSet) Then Exit Sub
Next lngSet
'Make sure something is entered in the text box
If txtEdit.Text = "" Then
MsgBox "You must enter a Part Number."
Exit Sub
End If
With ActiveSheet
'Make sure they are in the Part Number column
If Intersect(ActiveCell, .Columns(3)) Is Nothing Then Exit Sub
'Put the Part Number in the field.
ActiveCell.Value = txtEdit.Text
'Set the importance of the part.
If Me.Controls("optRequired") Then PartRequired
If Me.Controls("optChoice") Then PartChoice
If Me.Controls("optRecommended") Then PartRecommended
If Me.Controls("optOptional") Then PartOptional
End With
'Clear the controls
txtEdit.Text = ""
optRequired = True
txtEdit.SetFocus
Unload frmEdit
End Sub
Private Sub PartRequired()
'Sets the part importance to required by coloring the cells appropriately and setting the importance number in Column S.
MsgBox "This part is Required."
End Sub
Private Sub PartChoice()
'Sets the part importance to required by coloring the cells appropriately and setting the importance number in Column S.
MsgBox "This part is Required, but there is a choice to make."
End Sub
Private Sub PartRecommended()
'Sets the part importance to required by coloring the cells appropriately and setting the importance number in Column S.
MsgBox "This part is Recommended."
End Sub
Private Sub PartOptional()
'Sets the part importance to required by coloring the cells appropriately and setting the importance number in Column S.
MsgBox "This part is Optional."
End Sub
As always, I appreciate any help I can get. If I solve my problem myself, I will post my solution here.