Results 1 to 1 of 1

How to disable a form or controls at runtime

Threaded View

noclass1980 How to disable a form or... 10-24-2012, 12:47 AM
  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    27

    How to disable a form or controls at runtime

    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
    Last edited by noclass1980; 10-24-2012 at 12:50 AM. Reason: remove typos and improve clarity

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1