VBA: How to create a dynamic user form that adjusts controls based on input type?
I'm working on a project and hit a roadblock... I'm following best practices but I just started working with Quick question that's been bugging me - I'm creating a user form in Excel VBA that needs to dynamically adjust its controls based on the user's input type. For instance, if the user selects 'Text', I want to display a TextBox. If they select 'Number', I'd like to show a NumericUpDown control. I'm using Excel 2016 and I've tried using the Change event for a ComboBox to trigger the updates, but I'm having trouble with the layout and visibility of the controls. Here's a simplified version of what I have: ```vba Private Sub UserForm_Initialize() With Me.ComboBox1 .AddItem "Text" .AddItem "Number" .AddItem "Date" End With End Sub Private Sub ComboBox1_Change() Dim ctrl As Control For Each ctrl In Me.Controls If TypeName(ctrl) <> "ComboBox" Then ctrl.Visible = False End If Next ctrl Select Case Me.ComboBox1.Value Case "Text" Me.TextBox1.Visible = True Case "Number" Me.NumericUpDown1.Visible = True Case "Date" Me.DatePicker1.Visible = True End Select End Sub ``` However, when I run the form and select different options, the controls do not appear as expected. Sometimes, I receive a runtime error stating `Object variable or With block variable not set` specifically when trying to access `Me.NumericUpDown1`. I suspect it might be due to how these controls are initialized or perhaps their visibility is not being managed correctly. Could someone help troubleshoot this or suggest a better approach to dynamically manage controls on the user form? Thanks for any insights you can provide! For context: I'm using Vba on Ubuntu. What's the best practice here?