• Conditionally change focus on user form

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Conditionally change focus on user form

    Author
    Topic
    #499248

    I have a user From with 6 input boxes. One of those receives a strike price. If that price is lower than a the purchase price I want to tell the operator and then depending on the response move the focus back to the purchase price text box. This is th code

    Private Sub Opt5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Opt5.Value < Opt2.Value Then
    Response = MsgBox("The Strike Prices is lower than the Purchase Price is this correct", vbYesNo, "Check Strike Price")
    If Response = vbNo Then
    Opt2.SetFocus
    Cancel = True
    Exit Sub
    End If
    End If
    End Sub

    For some reason it asks the question twice even if I click No the first time and then I get an error at the Opt2.Setfocus command.

    This is the error message

    40018-Screen-Shot-2015-03-28-at-12.37.06-PM

    Any suggestions as to the solution greatly appreciated I have searched on line about moving the focus without any luck.

    Thanks

    Peter

    Viewing 8 reply threads
    Author
    Replies
    • #1497770

      Peter,

      Delete the Cancel=True line or change to Cancel=False for clarity. This sends the control back to the Opt5 control when you want focus to go to the Opt2 control. The Yes option should set the Cancel=True flag.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1497772

        Thanks for that suggestion, there is not a yes option unless I should include an else option in the if then but if the test is passed I want to move on. Deleting the cancel = true for some reason moves the focus to a command button not the next text box.

        Take care

        Peter

    • #1497777

      Peter,

      This code works.

      Private Sub tboxStrikePrice_Exit(Cancel As Integer)

      Dim iAns As Integer

      If CDbl(tboxStrikePrice.Value) Tab Order (at the right). You could also add an Else clause and manually set the focus.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1497813

        RG Thank you

        Still no luck I changed your code to conform with my UserForm hopefully I did that correctly see below.

        Private Sub Opt5_Exit(ByVal Cancel As MSForms.ReturnBoolean)

        Dim iAns As Integer

        If CDbl(Opt5.Value) 0 Then ‘Check to make sure Shares have been entered in multiples of 100
        MsgBox “The number of shares must be in Units of 100”
        Cancel = True
        With Me.Opt4 ‘Selects the previous entry so it can be overwritten
        .SelStart = 0
        .SelLength = Len(.Text)
        End With
        End If
        End Sub

        Private Sub Opt5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Dim iAns As Integer
        If CDbl(Opt5.Value) < CDbl(Opt2.Value) Then
        iAns = MsgBox("The Strike Prices is lower than the Purchase Price is this correct", _
        vbYesNo, "Check Strike Price")
        If iAns = vbNo Then
        Me.Opt2.SetFocus
        End If
        End If
        End Sub

        Private Sub UserForm_Activate()
        With EnterOptionData
        .Top = Application.Top + 125
        .Left = Application.Left + 600
        End With
        Opt1.SetFocus
        End Sub

        Private Sub UserForm_Initialize()
        Opt1.SetFocus
        Opt2.Text = Format(Number, "000.00")
        Opt3.Text = Format(Number, "000.00")
        Opt4.Text = Format(Number, "000.00")
        Opt5.Text = Format(Number, "000.00")
        End Sub

        Thank you and Take care

    • #1497875

      Peter,

      Would it be possible for you to upload a copy of the Excel file? This will make it much easier to see what is going on.

      One change you can make would be to turn off event processing in your initialization routine.

      Code:
      Private Sub UserForm_Initialize()
      
         [COLOR="#0000FF"]Application.EnableEvents = False[/COLOR]
            Opt1.SetFocus
            Opt2.Text = Format(Number, "000.00")
            Opt3.Text = Format(Number, "000.00")
            Opt4.Text = Format(Number, "000.00")
            Opt5.Text = Format(Number, "000.00")
         [COLOR="#0000FF"]Application.EnableEvents = True
      [/COLOR]
      End Sub 
      

      :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1497911

        Hi RG

        Do not have a problem passing the file onto you. However I would like to do it directly as there are lots of bits of code from other people and I am not sure I have made that clear through the routines. As I was only going to use it myself it was not an issue but if it gets into the public domain I would like to make sure it is done properly. Can you let me know if I can send to you?

        Have not tried your late suggestion will do and let you know thanks again for all your help.

        Take care

        peter

      • #1497914

        RG

        Tried Application.enablevents= False/True same result

        thanks

        Peter

    • #1498078

      Mitch,

      Try this code in the form module.

      HTH,
      Maud

      Code:
      Private Sub Opt5_AfterUpdate()
      If Val(Opt5.Value) < Val(Opt2.Value) Then
          Response = MsgBox("The Strike Prices is lower than the Purchase Price is this correct", vbYesNo, "Check Strike Price")
          If Response = vbNo Then
             Opt2.SetFocus
          End If
       End If
      End Sub
      
      • #1498141

        Hi Maud

        Thanks for the input tried that and it does not cycle twice as it did on exit but it still goes to the command button “Next Stock” bypassing the next text box Opt 6 which is what I would have thought it would have gone to had the set focus command not worked. If the strike price is greater than the purchase price then it does just tab to the Opt6. It’s got me beat,I can live with it just not what is needed, it could be the strike is wrong as opposed to the purchase price however I wanted to start ta the first one and work back down the form.

        Thanks again for your time

        Peter

    • #1498207

      Peter,

      I sent you a private message where you could send the file but haven’t seen one yet? :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1498224

      RG

      I think I have messed it up again I tried to reply using the email address you provided and was asked to authentic and that failed as well do you have my files yet?

      Sorry

      peter

      • #1498233

        Peter,

        My apologies! I just realized the code I posted as working was Access NOT Excel! I don’t know why the Excel version doesn’t work but it doesn’t and I can’t get your file to work as it should nor can I get a test file of my own to work either. I’m going to have to give this some deep thought. Maybe someone else will chime in with something I’m missing. BTW: I found a label and a textbox on your form that were located off of the form the Top value was minus (TextBox1 & Label1). :cheers:

        May the Forces of good computing be with you!

        RG

        PowerShell & VBA Rule!
        Computer Specs

    • #1498227

      Peter,

      Just got it. I’ll be in touch. :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1498241

      Peter,

      Ok I think I have the form working?

      What I did was to strip all the code that was not necessary to testing the form.

      I also completely redid the form and gave all the objects names so things are easier to understand when reading the code, e.g. tbStrikePrice vs Opt5).

      I’ve also changed some of the code (which of course you can change back if you don’t like it) which IMHO is easier to read and for the user to use.

      Test out the the file attached and see what you think.

      Test File: 40062-PeterFormTest-RG-

      Note if you like the changes you can Export the Form (it will take the form code with it) via the file menu in the VBE and then Import it into your file after deleting the old form. Of course you have to do a search and replace on the form name from old to new in your entire project.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1499092

      My first post, I am not trying to take-over. Just a keen VBA’er and curious about the outcome.

      I don’t know if Excel 2010 has instabilities as Excel 2003 does, especially bloat.
      – If a UserForm is edited a lot at design-time the code can become very twitchy.

      I’ll butt out here — my respect to “Retired Geek” 🙂

    Viewing 8 reply threads
    Reply To: Conditionally change focus on user form

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: