Can a slider control be added to an Excel VBA userform programmatically similar to frames, labels, e-CodePudding

I am trying to get Microsoft Slider controls added programmatically to a userform without success. While the standard controls in the Microsoft 2.0 Object library work fine calling them via the designer with a macro such as below:

Sub NewForm()

Dim TempForm As Object
Dim NewLabel As MSForms.Label
Dim NewFrame As MSForms.Frame

Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
With TempForm
 .Properties("Height") = Worksheets("PanelSpec").Range("F7").Value
 .Properties("Width") = Worksheets("PanelSpec").Range("F8").Value
 .Properties("Caption") = ""
End With

Set NewFrame = TempForm.designer.Controls.Add("Forms.frame.1")
With NewFrame
.Name = Worksheets("PanelSpec").Range("P6").Value
.Caption = Worksheets("PanelSpec").Range("P10").Value
.Height = Worksheets("PanelSpec").Range("P12").Value
.Left = Worksheets("PanelSpec").Range("P13").Value
.Top = Worksheets("PanelSpec").Range("P14").Value
.Width = Worksheets("PanelSpec").Range("P15").Value
.BorderStyle = 1
.SpecialEffect = 0
End With

End Sub

I have had no succuss at all adding slider controls which I believe come from a separate library, the 'Microsoft Slider Control v6'. It fails at every step to where I'm questioning if it is even possible. I would appreciate the VBA code if someone knows how to do it.

I have reasons to do it programmatically as this project will have >100 unique userforms and to reduce bloat I which to draw the individual userforms each time on the fly instead of storing all of them.

Thanks

CodePudding user response：

The following macro works to create a userform, frame and then add a Microsoft Windows Common Controls 6.0 slider and a MSForms label to the frame. The slider is not declared as I couldn't find a Dim spec that worked. It seems to work fine without it. Note the omission of .designer when adding controls to the frame and not the userform. Thanks to Rory for his helpful comments.

    Sub NewForm()
    
    Dim TempForm As Object
    Dim NewLabel As MSForms.Label
    Dim NewFrame As MSForms.Frame
    'Dim NewSlider As MSComctlLib.Slider
    
    Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
    With TempForm
    .Properties("Height") = Worksheets("PanelSpec").Range("F7").Value
    .Properties("Width") = Worksheets("PanelSpec").Range("F8").Value
    .Properties("Caption") = ""
    End With
    
    Set NewFrame = TempForm.designer.Controls.Add("Forms.frame.1")
    With NewFrame
    .Name = Worksheets("PanelSpec").Range("P6").Value
    .Caption = Worksheets("PanelSpec").Range("P10").Value
    .Height = Worksheets("PanelSpec").Range("P12").Value
    .Left = Worksheets("PanelSpec").Range("P13").Value
    .Top = Worksheets("PanelSpec").Range("P14").Value
    .Width = Worksheets("PanelSpec").Range("P15").Value
    .BorderStyle = 1
    .SpecialEffect = 0
    End With
    
    Set NewLabel = NewFrame.Controls.Add("Forms.label.1")
    With NewLabel
    .Name = Worksheets("PanelSpec").Range("V6").Value
    .BorderStyle = Worksheets("PanelSpec").Range("V8").Value
    .Caption = Worksheets("PanelSpec").Range("V9").Value
    .TextAlign = Worksheets("PanelSpec").Range("V10").Value
    .WordWrap = Worksheets("PanelSpec").Range("V11").Value
    .Font.Name = Worksheets("PanelSpec").Range("V12").Value
    .Font.Size = Worksheets("PanelSpec").Range("V13").Value
    .Height = Worksheets("PanelSpec").Range("V14").Value
    .Left = Worksheets("PanelSpec").Range("V15").Value
    .Top = Worksheets("PanelSpec").Range("V16").Value
    .Width = Worksheets("PanelSpec").Range("V17").Value
    End With
    
    Set NewSlider = NewFrame.Controls.Add("MSComctlLib.Slider.2")
    With NewSlider
    .Name = Worksheets("PanelSpec").Range("AI6").Value
    .Orientation = Worksheets("PanelSpec").Range("AI7").Value
    .TextPosition = Worksheets("PanelSpec").Range("AI8").Value
    .TickFrequency = Worksheets("PanelSpec").Range("AI9").Value
    .TickStyle = Worksheets("PanelSpec").Range("AI10").Value
    .LargeChange = Worksheets("PanelSpec").Range("AI11").Value
    .SelectRange = Worksheets("PanelSpec").Range("AI12").Value
    .SelLength = Worksheets("PanelSpec").Range("AI13").Value
    .SelStart = Worksheets("PanelSpec").Range("AI14").Value
    .SmallChange = Worksheets("PanelSpec").Range("AI15").Value
    .Max = Worksheets("PanelSpec").Range("AI16").Value
    .Min = Worksheets("PanelSpec").Range("AI17").Value
    .Height = Worksheets("PanelSpec").Range("AI18").Value
    .Left = Worksheets("PanelSpec").Range("AI19").Value
    .Top = Worksheets("PanelSpec").Range("AI20").Value
    .Width = Worksheets("PanelSpec").Range("AI21").Value
    End With

End Sub