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:

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.

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 ...

Code:

---

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

---

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

Even with using this in your initialize... ?? ..

Code:

---

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.

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. :)

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

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

You need to zip it and attach the zip file.

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")

From your UserForm code, on your Userform initialize event, take out the '1' in the procedure name. It should look like this ...

Code:

---

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? :dunno

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

No problem, glad you got it sorted. :)

Take care!

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?

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!).

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

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 :bink:

I'm going to have to give it a try

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

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 :bink:

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]

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

What should I use instead of a text box?

Quote:

---

Originally Posted by K. Georgiadis

What should I use instead of a text box?

---

Without looking into it properly, you could maybe use a combo box, refedit or input boxes....But hey - download and have a really good look at what MD's posted instead, play around with it if needs be, but I think he's got a solution for the problem of formatting the entries in text boxes :vv :bink:

PS Will attach a modified version below of what MD has done for you to look at also, but MD deserves all the credit for this one... :hi: