Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Conditionally change focus on user form

    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

    Screen Shot 2015-03-28 at 12.37.06 PM.png

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

    Thanks

    Peter

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Peter,

    This code works.
    Code:
    Private Sub tboxStrikePrice_Exit(Cancel As Integer)
    
       Dim iAns As Integer
       
       If CDbl(tboxStrikePrice.Value) < CDbl(tboxPurchPrice.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.tboxPurchPrice.SetFocus
          End If
       End If
       
    End Sub
    Note: The conversion of the Text values to numbers via CDbl()!

    TestSetFocus1.JPG

    If the cursor is not moving where you want it you need to check your Tab Order found on the Design Tab -> Tab Order (at the right). You could also add an Else clause and manually set the focus.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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) < 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
    It still cycle twice now does not give me an error but moves to the command button.

    Checked the tab ordered and it is Opt1 through Opt6 the label 1 through label 6 then the command button

    Screen Shot 2015-03-28 at 5.35.45 PM.png


    Just in case I am doing something stupid and I apologize for that; this is all the code attached to the user form.

    Private Sub NextStock_Click() 'Command Button that Opens the form again if more Symbols to be added
    ActiveCell.Offset(0, 0) = UCase(Opt1) 'Symbol
    ActiveCell.Offset(0, 1).Value = CCur(Opt2) 'Purchase Price
    ActiveCell.Offset(0, 2).Value = CCur(Opt3) 'Premium Received
    ActiveCell.Offset(0, 3).Value = CDec(Opt4) 'No of Shares
    ActiveCell.Offset(0, 4).Value = CCur(Opt5) 'Strike
    ActiveCell.Offset(0, 5).Value = CDate(Opt6) 'Expiry
    ActiveCell.Offset(0, 6).Value = CCur(Opt3) * CCur(Opt4) 'Total Premium
    ActiveCell.Offset(0, 0).Select
    Unload Me
    End Sub

    Private Sub Opt4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Opt4.Value Mod 100 > 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

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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()
    
       Application.EnableEvents = False
          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")
       Application.EnableEvents = True
    
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    mitchbvi (2015-03-29)

  8. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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

  9. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    RG

    Tried Application.enablevents= False/True same result

    thanks

    Peter

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    mitchbvi (2015-03-31)

  12. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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

  13. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Peter,

    I sent you a private message where you could send the file but haven't seen one yet?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #12
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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

  15. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Peter,

    Just got it. I'll be in touch.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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).
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  17. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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: PeterFormTest-RG .xls

    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

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