Announcement

Collapse
No announcement yet.

Userform Initialize vs Userform Show

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    Re: Userform Initialize vs Userform Show

    Hi,

    This should get your Userform2 displayed.
    See inline comments as to what is causing problems.
    Code:
    Sub Userform_Initialize()
        
        'Clear controls for next entry and set default box
        
        
        ' TextShares is linked to worksheet
        Range(TextShares.ControlSource).Value = ""
    '    TextShares.Value = ""
        TextPrice.Text = ""
       
        'Populate text boxes with data from the spreadsheet
    
    'TextTicker already has controlsource set to Sheet1!C14
    '    TextTicker.Text = Range("Sheet1!C4").Value
    
    ' Range C7 is a lookup formula that is currently #N/A
    ' same test applied to subsequent controls
        If Application.WorksheetFunction.IsNA(Range("Sheet1!C7")) Then
            TextCountry.Text = ""
        Else
            TextCountry.Text = Range("Sheet1!C7").Value
        End If
        
        If Application.WorksheetFunction.IsNA(Range("Sheet1!C6")) Then
            TextCurrency.Text = ""
        Else
            TextCurrency.Text = Range("Sheet1!C6").Value
        End If
        If Application.WorksheetFunction.IsNA(Range("Sheet1!C8")) Then
            TextDate.Text = ""
        Else
            TextDate.Text = Range("Sheet1!C8").Value
        End If
        
        If Application.WorksheetFunction.IsNA(Range("Sheet1!C9")) Then
            TextExchangeRate.Text = ""
        Else
            TextExchangeRate.Text = Range("Sheet1!C9").Value
        End If
        
        TextShares.SetFocus
    
    End Sub

    Cheers
    Andy

    Comment


    • #17
      Re: Userform Initialize vs Userform Show

      The problem is not with your use of Userform_Initialize() that is the correct format. The problem is most likely within the code you have WITHIN Userform_Initialize()

      When the commandbutton in UserForm4 is clicked it calls

      UserForm2.Show - which does the following things
      - Check To See If UserForm2 has already been loaded
      - If Yes Then Display It On The Screen
      - If No Load Userform 2 (Which calls UserForm_Initialize()) and then display it


      Having UserForm_Initialize() in the module for userform 2 is what you want to do so it calls code when UserForm2 is loaded for the first time.

      The problem is that you needed to troubleshoot the error that was occuring when UserForm_Initialize() is called rather than rename it.

      The error is most likely occuring because of all of your code right here:

      TextShares, TextPrice, TextTicker are presumably text fields on UserForm2 correct? In the UserForm_Initialize procedure the form is just being loaded which means subcomponents like TextShares haven't been initialized yet. If TextShares hasn't been initialized you can't set it's members like .Value.
      Code:
          TextShares.Value = ""     
          TextPrice.Text = ""     
          TextShares.SetFocus
      
          'Populate text boxes with data from the spreadsheet     
          TextTicker.Text = Range("Sheet1!C4").Value
          TextCountry.Text = Range("Sheet1!C7").Value
          TextCurrency.Text = Range("Sheet1!C6").Value
          TextDate.Text = Range("Sheet1!C8").Value
          TextExchangeRate.Text = Range("Sheet1!C9").Value
      To change these values like TextShares.Value you have to wait till they have been loaded (initialized has completed).

      Instead of using UserForm_Initialize you want to use UserForm_Activate (activate is called after all of the form and its subcomponents have been initialized).
      Last edited by cytop; May 5th, 2011, 06:05. Reason: Added code tags...

      Comment


      • #18
        Re: Userform Initialize vs Userform Show

        Welcome to the forums, RhinoT.

        Thank you for providing a possible solution, but have you noticed the thread is over 5 years old? However, the original poster is still active on the board so perhaps he will have a solution at last

        One other thing. All code listings must be wrapped in code tags. You agreed to these rules when you joined the forum, but it might be useful if you click here and have another read. I will add the code tags for you, this time.

        Comment


        • #19


          Re: Userform Initialize vs Userform Show

          Hi everyone,
          New to these forums. However did have the same problem that the "userform_initialise()" sub did not seem to activate. Solved it as below, probably not supposed to paste code into this box.

          Regards John Connelly

          Sub WAGES_SHOW()
          '
          test = initialise()
          WagesCalculator.Show

          End Sub

          Function initialise()
          WagesCalculator.ComboBoxRates.AddItem "40.33" 'ListIndex = 0
          WagesCalculator.ComboBoxRates.AddItem "41.31" 'ListIndex = 1
          WagesCalculator.ComboBoxRates.AddItem "42.41" 'ListIndex = 2
          WagesCalculator.ComboBoxRates.AddItem "44.48" 'ListIndex = 0
          WagesCalculator.ComboBoxRates.AddItem "46.16" 'ListIndex = 1
          WagesCalculator.ComboBoxRates.AddItem "36.74" 'ListIndex = 2
          'Combo box values are column(1) values.
          WagesCalculator.ComboBoxRates.BoundColumn = 1
          'Set combo box to first entry
          WagesCalculator.ComboBoxRates.ListIndex = 0

          End Function

          Comment

          Working...
          X