Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 22

Thread: Keeping last entered values in a wizard

  1. #1

    Keeping last entered values in a wizard

    I have attached a small Excel file incorporating a wizard on Sheet1, where data are entered by the user and are transferred to the appropriate cells in Sheet1, Sheet2, and Sheet3.

    The following code is adapted the code from J. Walkenbach's book and, with my limited VBA skills, I'm stumped on the following point:

    right now the wizard text boxes are reset each time the wizard is restarted. How do I reprogram this, so that the last entered values are retained so that the user does not have to reenter everything?

    Code:

    Option Explicit 
    
    Private Sub CancelButton_Click()
    Dim Msg As String
    Dim Ans As Integer
    Msg = "Cancel the wizard?"
    Ans = MsgBox(Msg, vbQuestion + vbYesNo, APPNAME)
    If Ans = vbYes Then Unload Me
    End Sub
    
    Public Sub UserForm1_Initialize()
    MultiPage1.Value = 0
    UpdateControls
    End Sub
    
    Sub UpdateControls()
    Select Case MultiPage1.Value
    Case 0
    BackButton.Enabled = False
    NextButton.Enabled = True
    Case MultiPage1.Pages.Count - 1
    BackButton.Enabled = True
    NextButton.Enabled = False
    Case Else
    BackButton.Enabled = True
    NextButton.Enabled = True
    End Select
    End Sub
    
    Private Sub BackButton_Click()
    MultiPage1.Value = MultiPage1.Value - 1
    UpdateControls
    End Sub
    Private Sub NextButton_Click()
    MultiPage1.Value = MultiPage1.Value + 1
    UpdateControls
    End Sub
     
    Private Sub FinishButton_Click()
    With ActiveWorkbook
    .Worksheets("sheet1").Range("b3").Value = Me.TextBox1.Value
    .Worksheets("sheet2").Range("a9").Value = Me.TextBox2.Value
    .Worksheets("sheet3").Range("a9").Value = Me.TextBox3.Value
    End With
    Unload Me
    End Sub
     
    Private Sub UserForm_Click()
    End Sub
    Your help is greatly appreciated.

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi,

    First off, take a look at the VBA tags we have here when posting, it will make your code look like mine does below.

    Add this to your userform_initialize event, see if it helps ...

    Private Sub CancelButton_Click()
        Dim Msg As String
        Dim Ans As Integer
        Msg = "Cancel the wizard?"
        Ans = MsgBox(Msg, vbQuestion + vbYesNo, APPNAME)
        If Ans = vbYes Then Unload Me
    End Sub
    
    Public Sub UserForm1_Initialize()
        MultiPage1.Value = 0
        UpdateControls
        With ActiveWorkbook
            Me.TextBox1.Value = .Worksheets("sheet1").Range("b3").Value
            Me.TextBox2.Value = .Worksheets("sheet2").Range("a9").Value
            Me.TextBox3.Value = .Worksheets("sheet3").Range("a9").Value
        End With
    End Sub
    
    Sub UpdateControls()
        Select Case MultiPage1.Value
        Case 0
            BackButton.Enabled = False
            NextButton.Enabled = True
        Case MultiPage1.Pages.Count - 1
            BackButton.Enabled = True
            NextButton.Enabled = False
        Case Else
            BackButton.Enabled = True
            NextButton.Enabled = True
        End Select
    End Sub
    
    Private Sub BackButton_Click()
        MultiPage1.Value = MultiPage1.Value - 1
        UpdateControls
    End Sub
    
    Private Sub NextButton_Click()
        MultiPage1.Value = MultiPage1.Value + 1
        UpdateControls
    End Sub
    
    Private Sub FinishButton_Click()
        With ActiveWorkbook
            .Worksheets("sheet1").Range("b3").Value = Me.TextBox1.Value
            .Worksheets("sheet2").Range("a9").Value = Me.TextBox2.Value
            .Worksheets("sheet3").Range("a9").Value = Me.TextBox3.Value
        End With
        Unload Me
    End Sub

  3. #3
    I replaced my VBA code with yours on the UserForm, but the text boxes still reset to null (empty) upon reactivating the wizard

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Even with using this in your initialize... ?? ..


    TextBox1.Value = sheets("sheet1").Range("b3").Value 
            TextBox2.Value = sheets("sheet2").Range("a9").Value 
            TextBox3.Value = sheets("sheet3").Range("a9").Value
    If no go, zip and upload the file.

  5. #5
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    K. Georgiadis

    I changed your original post to use our VBA tags. Doesn't it look better? To use these tags for code just put (VBA) before the code and (/VBA) after the code. Use square brackes [ ] instead of parentheses though.

  6. #6
    Yes, I used those statements in my initialize, but on reactivation the wizard still clears all the values previously entered

  7. #7
    thanks for the tip on attaching VBA tags. Is there a restriction on attaching .xls files? I tried earlier but couldn't do it

  8. #8
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You need to zip it and attach the zip file.

  9. #9

    keeping last entered values in a UserForm text box

    thanks, it had been a while since I posted and I had totally forgotten about the need to zip the attachment. So for those, who have been trying to help me, here is the file; I'm still trying to resolve two aspects of this:

    1) format the text boxes in the UserForm itself, so that the numbers are automatically entered in the correct format (e.g., % for TextBox1)
    2) Upon reactivation, I want the wizard to display the last entered values (right now, all the boxes are cleared upon "update")

  10. #10
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    From your UserForm code, on your Userform initialize event, take out the '1' in the procedure name. It should look like this ...

    Public Sub UserForm_ Initialize()
    And in your percent text box, you can enter one of two ways to keep the number as percent. 1) '20%'. Which means having the user input the percent symbol after their entry. 2) '.20'. Which means having the user input the number as a decimal. Either of those two ways will result in a percent (given the cell is formatted as a percent.

    Some questions. Why have 3 multi pages? And why have the inputs on 3 seperate sheets? And why even use the Public Const just for one name?

  11. #11
    Removing the "1" from "UserForm has done the trick. I'll play with the formatting next.

    Regarding your questions:
    1) I have a 3-page "wizard" because it looks like the real thing
    2) Entering the data in 3 separate worksheets is a real requirement. The final workbook will have multiple sections (perhaps as many as 30) performing calculations with the key parameters provided by the "wizard"

    Again, thanks for your help

  12. #12
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    No problem, glad you got it sorted.

    Take care!

  13. #13
    I solved all the problems except one that is driving me nuts: I got the % and the general number formats (both with a single decimal point) working just fine.

    The Comma Separated format #,##0 refuses to work. When the number is first entered, it is displayed correctly (e.g., 5,000), but when the wizard is reactivated it is displayed as 5000. On the other hand, the last entered numbers in % and general number formats continue to be displayed correctly.

    My recourse is to format the destination cell, so that the number is displayed properly there, but it puzzles me that this format is the only one that won't work. Any ideas why?

  14. #14
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Excel sees a number (5000) w/ the format #,##0 as just '5000', not '5,000', so that's why it's appearing that way in your text box upon re-activation. It's doing just what you tell it to. As to get it in that format, I think it would not be worth the trouble, imho. Unless there's a (userform) textbox format that I don't know about (which very well could be!).

  15. #15
    your opinion coincides with mine. I hate leaving as puzzle without solving it, but I have better things to do right now :-)

    Again thanks for your willingness to help

  16. #16
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Hi K. Georgiadis Is this what you're looking for maybe?

    [VBA] Private Sub FinishButton_Click()
    With ActiveWorkbook
    .Worksheets("sheet1").Range("b3").Value = Me.TextBox1.Value
    .Worksheets("sheet2").Range("a9").Value = Me.TextBox2.Value
    With Worksheets("sheet2").Range("a9")
    .NumberFormat = "#,##0" 'formats the cell for you
    End With
    .Worksheets("sheet3").Range("a9").Value = Me.TextBox3.Value
    End With

    Unload Me

    End Sub[/VBA]

    Regards,
    John

  17. #17
    I'm going to have to give it a try

  18. #18
    Nope, it formats the number temporarily but when the wizard is reactivated the format reverts to a general format, without a comma separator. I'm giving up on this one...Thanks for your help

  19. #19
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Understood, but here's the basis for the underlying problem (my 2 cents worth)....

    A text box automatically formats the cell using text format. i.e. the number (etc) is displayed exactly as entered without any dollar symbol or comma added to it. If you actually type in the dollar and comma when you make the entry in the text box then THAT is how it will be displayed instead and it will "fix".

    The trouble is that each time you select a cell and make an entry with a text box it will automatically be assumed (BECAUSE it's a text box) that you are going to use text format, so you must specify any different format that you want to use for that cell.

    If you HAVE already specified a format and made your entry, it will be displayed in that format (as I've shown above & below) - however, the instant the cell is RE-selected to view in the text box, it will be assumed that another entry {is to be}/{has been} made and it will be reformatted in 'text format' for the 'new' entry (even though no new entry was actually made)

    What you need to do "keep" it as it was is to write code so that your new code 're-enters' the value and re-formats the cell every time it's been "looked at" (selected) OR, - use something other than a text box for your entry....

    John

    PS here's another way - using your style - of doing the same thing as before....

    [VBA]
    .Worksheets("sheet1").Range("b3").Value = Me.TextBox1.Value
    .Worksheets("sheet2").Range("a9").NumberFormat = "$ #,##0"
    .Worksheets("sheet2").Range("a9").Value = Me.TextBox2.Value
    [/VBA]

  20. #20
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi,
    Have a look at the attachment. It does most things you're after (I hope). There are some userform bugs with tab order etc. to be tidied up.
    MD

Posting Permissions

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