Hi,
I want to import a form for the user to view but not use. The code below uses a user selected variable to import a form (I'm using a dummy form called "Test_Form" to get the macro working) but i don't want any of the text boxes, control buttons to be active so the user can't accidentally press a form control and try to run code that isn't valid in this instance. I have code that will open the form but I cannot disable the form of the buttons. I've tried the usual "For each Control in form" approach but this doesn't work. Can anyone suggest why the line
VBA.UserForms.Add(FormExampleFileName2).Enabled = False
in the code below does't work? The macro doesn't fall over, it just carries on without disabling the form. i've put coment lines in to explain each step.
Public Sub ImportForm()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim Form2Show
'Identifies form to import from a worksheet cell. in this case its a form called
'Test_Form.frm with a single CommnadButton
FormFilePath = Sheets("DataBase").Range("N" & EntryNumberText + 6)
'strips "Test_Form.frm" from the full file path
FormExampleFileName1 = Split(FormFilePath, "\")(UBound(Split(FormFilePath, "\")))
'strips "Test.Form" from "Test_Form.frm"
FormExampleFileName2 = Split(FormExampleFileName1, ".")(LBound(Split(FormExampleFileName1, ".")))
'Test_Form successfully imported
ThisWorkbook.VBProject.VBComponents.Import (FormFilePath)
VBA.UserForms.Add(FormExampleFileName2).Hide
'number of Controls on "Test_Form" counted (=1 as just a single CommandButton)
NumofControls = VBA.UserForms.Add(FormExampleFileName2).Controls.Count
VBA.UserForms.Add(FormExampleFileName2).Show
'Form or CommandButton not disabled!
VBA.UserForms.Add(FormExampleFileName2).Enabled = False
'Successfully removes "Test_Form"
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(FormExampleFileName2)
VBProj.VBComponents.Remove VBComp
End Sub
Thanks in advance
Bookmarks