Radio Buttons on User Forms

[LordDragon]

Greetings,


I have created the following user form so the users can edit the parts, or add new ones.

Edit Parts Form.png


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.

[Bob Phillips]

I got a bit lost with all that commenting out and stuff,but couldn't you just use

Selection.EntireRow

and do what you need to do to that object?

[LordDragon]

I was a little worried my description might be a little confusing.

How, where exactly should I put the Selection.EntireRow part?

This part checks to make sure the user has selected only a cell in column C. If that is not what is selected, the nothing happens when they click the OK button.

    With ActiveSheet
        'Make sure they are in the Part Number column
        If Intersect(ActiveCell, .Columns(3)) Is Nothing Then Exit Sub

That's exactly the part I'm having trouble with.

How do I get from that selected cell to using the same row, column S to enter the number? And that same row Columns A - E to shade and apply the border?

[Bob Phillips]

I am guessing you mean something like

With ActiveSheet 
     'Make sure they are in the Part Number column
    If Intersect(ActiveCell, .Columns(3)) Is Nothing Then Exit Sub 
    .Cells(Activecell.Row,"S").Value = txtEdit.Text
    With .Cells(Activecell.Row,"A").Resize(,5)

        'apply shading and border
    End With

[LordDragon]

That's very close.

I'm thinking that if I put this in the section for each radio button (i.e. PartOptional); then I can change it as follows:

With ActiveSheet
	.Cells(ActiveCell.Row, "S").Value = 4
	Wtih .Cells(ActiveCell.Row, "A").Resize(,5)
	
	'apply shading and border code
End With

I'll give that a try and see what happens.

[Bob Phillips]

I would have one routine that all of he buttons call with an id as to which called, such as

Private Sub PartOptional() 
     Call RadioAction(4)     
End Sub 

Private Function RadioAction(ByVal Id as Long) As Boolean
With ActiveSheet 
    .Cells(ActiveCell.Row, "S").Value = Id 
     Call ApplyBorders(.Cells(ActiveCell.Row, "A").Resize(,5))
     Call ApplyColour(.Cells(ActiveCell.Row, "A").Resize(,5))
End With
End Function

Private Function ApplyBorders(ByRef rng As Range)
     'Applies the borders to the cells on the sheet.

    With rng
    
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        
        .BorderAround LineStyle:=xlContinuous, ColorIndex:=xlAutomatic, Weight:=xlThin
     
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End With
End Function

[LordDragon]

Thanks. I'll give that a try.

I just tried this code. It worked as far as it put the number 4 in column S on that row and shaded the correct color, but it colored the entire sheet. Oops.

Private Sub PartOptional()
'Sets the part importance to required by coloring the cells appropriately and setting the importance number in Column S.


    With ActiveSheet
        .Cells(ActiveCell.Row, "S").Value = 4
        With .Cells(ActiveCell.Row, "A").Resize(, 5)
            Cells.Interior.Color = RGB(204, 255, 255)
        End With
    End With


End Sub

[Bob Phillips]

You have set the range in the With, so you should colour that. By adding Cells before the colour statements, you are colouring the whole sheet

Private Sub PartOptional() 
     'Sets the part importance to required by coloring the cells appropriately and setting the importance number in Column S.
     
     
    With ActiveSheet 
        .Cells(ActiveCell.Row, "S").Value = 4 
        With .Cells(ActiveCell.Row, "A").Resize(, 5) 
            .Interior.Color = RGB(204, 255, 255) 
        End With 
    End With 
End Sub

[Bob Phillips]

If you had used .Cells instead of just Cells, whilst not necessary, it would have worked.

[LordDragon]

I see what you mean there. It's amazing how that one little dot can make all the difference.

I was looking at the other suggestion you made. About one routine that is called by all the buttons and then modified slightly by the use of the Id.

I think if I put a Select Case feature in there for the colors it would work. But I'm a little confused about the rng, is that being set by this code?

        Call ApplyBorders(.Cells(ActiveCell.Row, "A").Resize(,5)) 
        Call ApplyColour(.Cells(ActiveCell.Row, "A").Resize(,5))

If so, then I just need to make another Function called "ApplyColour" and set the rng the same as the ApplyBorders function, correct?

[Bob Phillips]

Yes, you are correct in your assumptions, that is what I was implying in that code. If the different option button will generate a different colour, you can use a Case statement, but you should also pass the id to the ApplyColour routine, or put he Case in that calling module and pass the RGB colour.

[LordDragon]

Ok.

I tried it. Here is the code I came up with.

Private Function ApplyColour(ByRef rng As Range, ByVal Id As Long)
'Applies the color to the cells.


    With rng
    
        Select Case Id
            Case 1
                .Interior.Color = RGB(255, 128, 128)
            Case 2
                .Interior.Color = RGB(255, 255, 0)
            Case 3
                .Interior.Color = RGB(0, 255, 128)
            Case 4
                .Interior.Color = RGB(204, 255, 255)
        End Select
    End With
    
End Function

But when I try to run it I get a Compile Error saying "Argument Not Optional" and it's highlighting this part of the call code.

Call ApplyColour(.Cells(ActiveCell.Row, "A").Resize(,5))

[Bob Phillips]

As I said, you have to pass Id to the procedure ApplyColour, as well as the range.

[LordDragon]

I thought that's what the "ByVal Id As Long" part was doing.

[Bob Phillips]

No, that is setting up the procedure to take another parameter, but you have to pass it as well, otherwise what is the point of allowing it?

[LordDragon]

I've been trying to look up how to do that, but everything I'm finding is basically saying that putting the ByRef or ByVal thing in there is all I need to do.

[Bob Phillips]

Have you got a generic update procedure that you call from each of the OptionButton click events? If so, you must be passing the Id, to that, you just pass that on to ApplyColour, but as the second parameter

Call ApplyColour(.Cells(ActiveCell.Row, "A").Resize(,5), Id)

[LordDragon]

That worked.

Woohoo! Learned something new today.