Clear control boxes to default value on Userform

[PatHat]

Hi Everyone,

I'm working with Text and Combo boxes on a Tabstrip in a Userform. I have multiple Tabs on which the user will input data and then click a button to write the data to various worksheets. So far so good. My problem is that because Tabstrips share the same controls I want to reset the data in the Text and Combo boxes to their default values each time the user moves to the next tab on the strip.

First prize (if possible), would be for code for it to happen automatically as the user clicks to moves from say Tab1 to Tab2 on the Tabstrip. Not sure if this is possible?

Second option is to have a reset button which I have been playing around with various codes, but I cant get it to reset to the controls default value.

Here's what I've tried so far:

Private Sub Reset_Click()
'  Dim x As Control
'  For Each x In Me.Controls
'     If TypeOf x Is MSForms.TextBox Then x.Value = DefaultValue
'     Next
     
End Sub

That doesnt work because of the "DefaultValue" being incorrect. I tried = False or = True but it just writes those words into my text boxes of course. Using "" instead just leaves the boxes blank.

Next code I tried is this which resets to default values, so it works, but it jumps the Userform focus back to Tab1 each time. So if my user was on Tab1, and moves to Tab2 and hits the reset button, I need the focus to stay on Tab2, not jump back to Tab1. So maybe just an extra line of code is needed here to keep the focus on the current tab.

Private Sub Reset_Click()


Unload Me
UserForm1.Show


End Sub

Any help would be greatly appreciated as I have many Tabs and controls to work with so using a Multipage isnt an option. Tabstrip will be the quickest option.

Thanks in advance
Pat

 

[Jaafar Tribak]

@ PatHat

Are you setting the 'default' values at design time ?

 

[PatHat]

Yes I think. On the Properties sheet with say Textbox 1 selected I then set the Value field to what the default must be.

 

[Jaafar Tribak]

You could store the initial default values somewhere in a module level array, collection or the Textboxes Tag Properties then retrieve them back on Tab change event .

Something like this maybe: (Change the Tabstrip name as required)

Option Explicit

Private Sub UserForm_Initialize()
    Call Init
End Sub

Private Sub TabStrip1_Change()
    Call Init(Update:=True)
End Sub


Private Sub Init(Optional ByVal Update As Boolean = False)

    Dim x As Control
    
    For Each x In Me.Controls
        If TypeOf x Is MSForms.TextBox Then
            With Me.TabStrip1
                If x.Left >= .Left And x.Top > .Top And _
                x.Left + x.Width < .Left + .Width And x.Top + x.Height < .Top + .Height Then
                    If Update Then
                        x.Text = x.Tag
                    Else
                        x.Tag = x.Text
                    End If
                End If
            End With
        End If
    Next

End Sub

 

[PatHat]

Brilliant!! Thank you so much. Will help me immensely.

It seems to pull the default values just by filling in the Text field on the Properties sheet, so nice and easy