PDA

View Full Version : [SOLVED] Radio Buttons on User Forms



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.

Bob Phillips
12-31-2015, 04:03 AM
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
12-31-2015, 04:19 AM
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
12-31-2015, 04:30 AM
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
12-31-2015, 04:41 AM
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
12-31-2015, 04:50 AM
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
12-31-2015, 04:58 AM
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
12-31-2015, 05:01 AM
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
12-31-2015, 05:01 AM
If you had used .Cells instead of just Cells, whilst not necessary, it would have worked.

LordDragon
12-31-2015, 06:18 AM
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
12-31-2015, 06:37 AM
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
12-31-2015, 06:52 AM
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
12-31-2015, 06:55 AM
As I said, you have to pass Id to the procedure ApplyColour, as well as the range.

LordDragon
12-31-2015, 07:03 AM
I thought that's what the "ByVal Id As Long" part was doing.

Bob Phillips
12-31-2015, 09:24 AM
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
12-31-2015, 09:45 AM
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
12-31-2015, 10:33 AM
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
12-31-2015, 11:00 AM
That worked.

Woohoo! Learned something new today.