PDA

View Full Version : Solved: Generic Reset Sub for Userform



Mahahaava
02-25-2008, 06:40 PM
Hi,

I'm trying to write a subroutine that could be called from any UserForm to clear all CheckBoxes, ComboBoxes, TextBoxes, OptionButtons and what not in the UserForm. I'm having problems passing the Userform name to the SubRoutine. Anyone have a "done-deal" floating about?


Cheers,

/Petri

PS. Using Excel 2003

mikerickson
02-25-2008, 09:17 PM
Perhaps this, in a normal moduleSub AllControlsToNull(myUF As UserForm)
Dim oneControl As Control
Dim i As Long
For Each oneControl In myUF.Controls
With oneControl
Select Case TypeName(oneControl)
Case Is = "TextBox"
.Value = vbNullString
Case Is = "ComboBox"
.ListIndex = -1
Case Is = "ListBox"
For i = 0 To .ListCount - 1
.Selected(i) = False
Next i
Case Is = "CheckBox", "OptionButton", "ToggleButton"
.Value = False
Case Is = "SpinButton", "ScrollBar"
.Value = .Min
End Select
End With
Next oneControl
End Sub
Called with
Private Sub CommandButton2_Click()
Call AllControlsToNull(Me)
End Sub

Mahahaava
02-25-2008, 10:51 PM
Excellent! Thanks! I was actually almost there but that's what happens when you burn the candle on allnighters! Thanks again!

/Petri

mikerickson
02-25-2008, 10:56 PM
You're welcome.