Code building a Form, building a button with code....

A

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
422
I have been gnashing this problem for a while now, and I cannot find the resolution. I previously posted a question re: building an form with code and got that all working great for several forms. But now I have run into a problem that I cannot figure out.

I have the following code:
Code: 
...
            topPos = topPos + 24
            Set Btn = objFrm.Designer.Controls.Add("Forms.CommandButton.1")
            With Btn
                  .Caption = "Refresh View": .Height = 24: .Width = 78: .Left = 18: .Top = topPos:
            End With
            
            [COLOR=#FF0000]'GIVES ME AN ERROR SOMEWHERE IN THE CODE BLOCK BELOW[/COLOR]
            With objFrm.CodeModule
                  Line = .CountOfLines
                  .InsertLines Line + 1, "Sub " & Btn.Name & "_Click()"
                  .InsertLines Line + 2, "     Dim cCont As Control"
                  .InsertLines Line + 3, "     Dim lRow As Long"
                  .InsertLines Line + 4, "     customFunctions.disable_Defaults"
                  .InsertLines Line + 5, "     lRow = Sheets(""Headers"").Range(""A500"").end(xlUp).Row"
                  .InsertLines Line + 6, "     For v = lRow To 2 Step -1"
                  .InsertLines Line + 7, "          Select Case Me.Controls(""TextBox"" & v - 1)"
                  .InsertLines Line + 8, "               Case Is <> """":        Cells(v, 1) = Me.Controls(""TextBox"" & v - 1)"
                  .InsertLines Line + 9, "               Case Else:              Sheets(""Headers"").Range(""A"" & v).EntireRow.Delete"
                  .InsertLines Line + 10, "          End Select"
                  .InsertLines Line + 11, "     Next v"
                  .InsertLines Line + 12, "     Unload Me"
                  .InsertLines Line + 13, "     Call forms_SetUp.CreateFieldSetupForm"
                  .InsertLines Line + 14, "     enable_Defaults"
                  .InsertLines Line + 15, "End Sub"
            End With
[COLOR=#ff0000]            'GIVES ME AN ERROR SOMEWHERE IN THE CODE BLOCK ABOVE[/COLOR]
            
            Set Btn = objFrm.Designer.Controls.Add("Forms.CommandButton.2")
            With Btn
                  .Caption = "Save & Close": .Height = 24: .Width = 78: .Left = 114: .Top = topPos:
            End With
            
            ' Code for the Click() event of the "Save & Close" button.
            With objFrm.CodeModule
                  Line = .CountOfLines
                  .InsertLines Line + 1, "Sub " & Btn.Name & "_Click()"
                  .InsertLines Line + 2, "     customFunctions.disable_Defaults"
                  .InsertLines Line + 3, "     Thisworkbook.Save"
                  .InsertLines Line + 4, "     Unload Me"
                  .InsertLines Line + 5, "     Call forms_SetUp.DeleteAllForms"
                  .InsertLines Line + 6, "     enable_Defaults"
                  .InsertLines Line + 7, "End Sub"
            End With
      End With
...

It's driving me crazy because if I remove all the stops from the code, it just fails somewhere in the designated block. No error, it just exits the procedure without creating CommandButton.2.

There is some code prior to this block, and it works fine. All of the variables are declared, nothing is visually wrong that I can see. If I insert a break at the top of the "problem code block", it stops there and when I hit F8 it does fine until .InsertLines Line + 1, "Sub " & Btn.Name & "_Click()" and then tells me (understandably) that it can't enter break mode at this point.

If I put an error message in it says "error: -2147221005 Invalid Class String". I've googled this error and cannot find anything definitive to get me moving.

Can anyone see an issue?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
I should mention that the code does successfully create CommandButton.1 and the code it should contain. So I guess the error actually resides in
Code: 
[COLOR=#333333]Set Btn = objFrm.Designer.Controls.Add("Forms.CommandButton.2")[/COLOR]
somehow...
 
Last edited:
Upvote 0
That section of code works fine for me when I slot it into code that creates a form with multiple buttons.

PS What do you mean by 'put an error message' in?
 
Upvote 0
I didn't notice this,
Code: 
Set Btn = objFrm.Designer.Controls.Add("Forms.CommandButton.2")
as I was concentrating on the code you indicated was the the problem but it's definitely wrong.

If I change 'Forms.CommandButton.1' to 'Forms.CommandButton.2' in the code I mentioned previously I get the 'invalid class string error' with the first button it tries to create.
 
Upvote 0
Norie said:
I didn't notice this,
Code: 
Set Btn = objFrm.Designer.Controls.Add("Forms.CommandButton.2")
as I was concentrating on the code you indicated was the the problem but it's definitely wrong.

If I change 'Forms.CommandButton.1' to 'Forms.CommandButton.2' in the code I mentioned previously I get the 'invalid class string error' with the first button it tries to create.
Click to expand...


Thanks for the response, Norie.

RE: "'put an error message' in"--I put an errorcheck exit in the procedure in an attempt to get some information I could work with... I know you advised against that with code in development in an earlier post, but I was getting desperate. I've since taken it out.

Strange. I built 3 other forms with the same code and they don't have "2" in them. I changed it to "1". But now I get an "ambiguous name" error, which is unexpected because I use nearly identical code in 3 other forms I have built. They have 2 buttons each and the same structures.
 
Upvote 0
I think that the issue is that you haven't assigned a name to the command buttons so it's trying to default to "CommandButton1" for each one (when using Set Btn = objFrm.Designer.Controls.Add("Forms.CommandButton.1") for each). The following modification worked for me:

Code: 
Sub test()
Dim objForm As Object
Dim Btn As Control
Set objFrm = ThisWorkbook.VBProject.VBComponents("UserForm1")
 topPos = topPos + 24
            Set Btn = objFrm.Designer.Controls.Add("Forms.CommandButton.1"[COLOR=#ff0000],[/COLOR] [COLOR=#ff0000]"CmdBtn1"[/COLOR])
            With Btn
                  .Caption = "Refresh View": .Height = 24: .Width = 78: .Left = 18: .Top = topPos:
            End With
            
            'GIVES ME AN ERROR SOMEWHERE IN THE CODE BLOCK BELOW
            With objFrm.CodeModule
                  Line = .CountOfLines
                  .InsertLines Line + 1, "Sub " & Btn.Name & "_Click()"
                  .InsertLines Line + 2, "     Dim cCont As Control"
                  .InsertLines Line + 3, "     Dim lRow As Long"
                  .InsertLines Line + 4, "     customFunctions.disable_Defaults"
                  .InsertLines Line + 5, "     lRow = Sheets(""Headers"").Range(""A500"").end(xlUp).Row"
                  .InsertLines Line + 6, "     For v = lRow To 2 Step -1"
                  .InsertLines Line + 7, "          Select Case Me.Controls(""TextBox"" & v - 1)"
                  .InsertLines Line + 8, "               Case Is <> """":        Cells(v, 1) = Me.Controls(""TextBox"" & v - 1)"
                  .InsertLines Line + 9, "               Case Else:              Sheets(""Headers"").Range(""A"" & v).EntireRow.Delete"
                  .InsertLines Line + 10, "          End Select"
                  .InsertLines Line + 11, "     Next v"
                  .InsertLines Line + 12, "     Unload Me"
                  .InsertLines Line + 13, "     Call forms_SetUp.CreateFieldSetupForm"
                  .InsertLines Line + 14, "     enable_Defaults"
                  .InsertLines Line + 15, "End Sub"
            End With
            'GIVES ME AN ERROR SOMEWHERE IN THE CODE BLOCK ABOVE
            
            Set Btn = objFrm.Designer.Controls.Add("Forms.CommandButton.1"[COLOR=#ff0000], "CmdBtn2"[/COLOR])
            With Btn
                  .Caption = "Save & Close": .Height = 24: .Width = 78: .Left = 114: .Top = topPos:
            End With
            
            ' Code for the Click() event of the "Save & Close" button.
            With objFrm.CodeModule
                  Line = .CountOfLines
                  .InsertLines Line + 1, "Sub " & Btn.Name & "_Click()"
                  .InsertLines Line + 2, "     customFunctions.disable_Defaults"
                  .InsertLines Line + 3, "     Thisworkbook.Save"
                  .InsertLines Line + 4, "     Unload Me"
                  .InsertLines Line + 5, "     Call forms_SetUp.DeleteAllForms"
                  .InsertLines Line + 6, "     enable_Defaults"
                  .InsertLines Line + 7, "End Sub"
            End With
End Sub

Regards,

CJ
 
Upvote 0
MrIfOnly said:
I think that the issue is that you haven't assigned a name to the command buttons so it's trying to default to "CommandButton1" for each one (when using Set Btn = objFrm.Designer.Controls.Add("Forms.CommandButton.1") for each). The following modification worked for me:

Code: 
Sub test()
Dim objForm As Object
Dim Btn As Control
Set objFrm = ThisWorkbook.VBProject.VBComponents("UserForm1")
 topPos = topPos + 24
            Set Btn = objFrm.Designer.Controls.Add("Forms.CommandButton.1"[COLOR=#ff0000],[/COLOR] [COLOR=#ff0000]"CmdBtn1"[/COLOR])
            With Btn
                  .Caption = "Refresh View": .Height = 24: .Width = 78: .Left = 18: .Top = topPos:
            End With
            
            'GIVES ME AN ERROR SOMEWHERE IN THE CODE BLOCK BELOW
            With objFrm.CodeModule
                  Line = .CountOfLines
                  .InsertLines Line + 1, "Sub " & Btn.Name & "_Click()"
                  .InsertLines Line + 2, "     Dim cCont As Control"
                  .InsertLines Line + 3, "     Dim lRow As Long"
                  .InsertLines Line + 4, "     customFunctions.disable_Defaults"
                  .InsertLines Line + 5, "     lRow = Sheets(""Headers"").Range(""A500"").end(xlUp).Row"
                  .InsertLines Line + 6, "     For v = lRow To 2 Step -1"
                  .InsertLines Line + 7, "          Select Case Me.Controls(""TextBox"" & v - 1)"
                  .InsertLines Line + 8, "               Case Is <> """":        Cells(v, 1) = Me.Controls(""TextBox"" & v - 1)"
                  .InsertLines Line + 9, "               Case Else:              Sheets(""Headers"").Range(""A"" & v).EntireRow.Delete"
                  .InsertLines Line + 10, "          End Select"
                  .InsertLines Line + 11, "     Next v"
                  .InsertLines Line + 12, "     Unload Me"
                  .InsertLines Line + 13, "     Call forms_SetUp.CreateFieldSetupForm"
                  .InsertLines Line + 14, "     enable_Defaults"
                  .InsertLines Line + 15, "End Sub"
            End With
            'GIVES ME AN ERROR SOMEWHERE IN THE CODE BLOCK ABOVE
            
            Set Btn = objFrm.Designer.Controls.Add("Forms.CommandButton.1"[COLOR=#ff0000], "CmdBtn2"[/COLOR])
            With Btn
                  .Caption = "Save & Close": .Height = 24: .Width = 78: .Left = 114: .Top = topPos:
            End With
            
            ' Code for the Click() event of the "Save & Close" button.
            With objFrm.CodeModule
                  Line = .CountOfLines
                  .InsertLines Line + 1, "Sub " & Btn.Name & "_Click()"
                  .InsertLines Line + 2, "     customFunctions.disable_Defaults"
                  .InsertLines Line + 3, "     Thisworkbook.Save"
                  .InsertLines Line + 4, "     Unload Me"
                  .InsertLines Line + 5, "     Call forms_SetUp.DeleteAllForms"
                  .InsertLines Line + 6, "     enable_Defaults"
                  .InsertLines Line + 7, "End Sub"
            End With
End Sub

Regards,

CJ
Click to expand...

CJ<

Well, that seems to have taken care of the button issue. The buttons are both being built correctly with their respective code. Thanks! Of course, it reveals another issue on the next code block, but it may be the same problem so I'll work it out.

Strangely, I have several other procedures that build forms on the fly using the exact same code, and none of them fail. I have to wonder why they work so well and this one decided to fail? Unless they no longer work and I just haven't checked them recently...:eeek:

Based on your signature, I owe you either a dollar, 2 dollars or 3 dollars. In any event, I owe you. Thanks!
 
Upvote 0
It doesn't surprise me that you having problems with this: I've noticed that when it comes to form building with VBA quirkiness abounds.

And, yes, you owe me big time! :)

CJ
 
Upvote 0
You must log in or register to reply here.

Similar threads

SeniorNewbie
  • Question Question
Problem with VBIDE.CodeModule
Replies
3
Views
330
Domenic
D
P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
116
6StringJazzer
6StringJazzer
T
  • Question Question
MrExcel Code to loop through Slicers not working
Replies
0
Views
562
TopCatz
T
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
99
Denny57
D
hendrikbez
  • Question Question
Cannot get to edit on my user form with 1 to 5 members on it
Replies
1
Views
268
Anthony47
A

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top