Save Value after run

[scantor145]

Excel 2003 w/VBA 6.3

Have a form with several textboxes in which a user will insert values.
The names of all the textboxes start with "txt", e.g. txtLotNum.
During the course of running the program I purposely "clear" (see below) the values from all textboxes. However, I would like to retain the value of one of the textboxes, so that when the user starts the program again after closing, that value already appears in the textbox on the form. Cuts down on data entry.

Please Login or Register  to view this content.

[Bob Phillips]

Save it in a workbook name


ThisWorkbook.Names.Add Name:="__SavedTextBox", RefersTo:="=" &
txtLotNum.Text
For Each Ctls In frmEasyLyteQC.Controls
If Left(Ctls.Name, 3) = "txt" Then
Ctls.Value = ""
End If
Next Ctls

You get it back with

myValue = Evaluate(ThisWorkbook).Names("__SavedTextBox").RefersTo)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"scantor145" <[email protected]> wrote
in message news:[email protected]...
>
> Excel 2003 w/VBA 6.3
>
> Have a form with several textboxes in which a user will insert values.
> The names of all the textboxes start with "txt", e.g. txtLotNum.
> During the course of running the program I purposely "clear" (see
> below) the values from all textboxes. However, I would like to retain
> the value of one of the textboxes, so that when the user starts the
> program again after closing, that value already appears in the textbox
> on the form. Cuts down on data entry.
>
>
> Code:
> --------------------
> For Each Ctls In frmEasyLyteQC.Controls
> If Left(Ctls.Name, 3) = "txt" Then
> Ctls.Value = ""
> End If
> Next Ctls
> --------------------
>
>
> --
> scantor145
> ------------------------------------------------------------------------
> scantor145's Profile:
http://www.excelforum.com/member.php...o&userid=14766
> View this thread: http://www.excelforum.com/showthread...hreadid=533684
>

[Dave Peterson]

Another option may be to just use a hidden worksheet to save all the stuff you
need.

scantor145 wrote:
>
> Excel 2003 w/VBA 6.3
>
> Have a form with several textboxes in which a user will insert values.
> The names of all the textboxes start with "txt", e.g. txtLotNum.
> During the course of running the program I purposely "clear" (see
> below) the values from all textboxes. However, I would like to retain
> the value of one of the textboxes, so that when the user starts the
> program again after closing, that value already appears in the textbox
> on the form. Cuts down on data entry.
>
> Code:
> --------------------
> For Each Ctls In frmEasyLyteQC.Controls
> If Left(Ctls.Name, 3) = "txt" Then
> Ctls.Value = ""
> End If
> Next Ctls
> --------------------
>
> --
> scantor145
> ------------------------------------------------------------------------
> scantor145's Profile: http://www.excelforum.com/member.php...o&userid=14766
> View this thread: http://www.excelforum.com/showthread...hreadid=533684

--

Dave Peterson

[scantor145]

Thanks for the response , but it doesn't seem to work. I think there's something wrong with

Please Login or Register  to view this content.

Parentheses missing?
Should there be something after RefersTo?

[Bob Phillips]

Looks like a space has crept in

myValue = Evaluate(ThisWorkbook).Names("__SavedTextBox").RefersTo

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"scantor145" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks for the response , but it doesn't seem to work. I think there's
> something wrong with
>
>
> Code:
> --------------------
> myValue = Evaluate(ThisWorkbook).Names("__SavedTextBox").Ref ersTo)
> --------------------
>
>
> Parentheses missing?
> Should there be something after RefersTo?
>
>
> --
> scantor145
> ------------------------------------------------------------------------
> scantor145's Profile:
http://www.excelforum.com/member.php...o&userid=14766
> View this thread: http://www.excelforum.com/showthread...hreadid=533684
>

[scantor145]

Thanks again, but maybe I just don't understand where to put the line below.

I thought the idea was to somehow "save" the txtLotNum entry that had just been entered. I placed the code below just as the program starts.

I receive an Object required run-time error 424 message

Please Login or Register  to view this content.

The workbook wasn't closed, but even if it was, I thought that the txtLotNum value was "saved" someplace.

[Dave Peterson]

I didn't build the textboxes, but maybe this did work for me:

Option Explicit
Sub testme()

Dim txtLotNum As String
Dim myValue As String

txtLotNum = "hi there"

ThisWorkbook.Names.Add Name:="__SavedTextBox", RefersTo:=txtLotNum
myValue = Evaluate(ThisWorkbook.Names("__SavedTextBox").RefersTo)

MsgBox myValue

End Sub



scantor145 wrote:
>
> Thanks again, but maybe I just don't understand where to put the line
> below.
>
> I thought the idea was to somehow "save" the txtLotNum entry that had
> just been entered. I placed the code below just as the program
> starts.
>
> I receive an Object required run-time error 424 message
>
> Code:
> --------------------
> MyValue = Evaluate(ThisWorkbook).Names("SavedTextBox").RefersTo
> --------------------
>
> The workbook wasn't closed, but even if it was, I thought that the
> txtLotNum value was "saved" someplace.
>
> --
> scantor145
> ------------------------------------------------------------------------
> scantor145's Profile: http://www.excelforum.com/member.php...o&userid=14766
> View this thread: http://www.excelforum.com/showthread...hreadid=533684

--

Dave Peterson

[Bob Phillips]

You said ... so that when the user starts the
program again after closing, that value already appears in the textbox
on the form.

That piece of code will get the saved textbox value in a variable which you
can then load into your textbox on a subsequent run. You will have to decide
where to do that.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"scantor145" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks again, but maybe I just don't understand where to put the line
> below.
>
> I thought the idea was to somehow "save" the txtLotNum entry that had
> just been entered. I placed the code below just as the program
> starts.
>
> I receive an Object required run-time error 424 message
>
>
> Code:
> --------------------
> MyValue = Evaluate(ThisWorkbook).Names("SavedTextBox").RefersTo
> --------------------
>
>
> The workbook wasn't closed, but even if it was, I thought that the
> txtLotNum value was "saved" someplace.
>
>
> --
> scantor145
> ------------------------------------------------------------------------
> scantor145's Profile:
http://www.excelforum.com/member.php...o&userid=14766
> View this thread: http://www.excelforum.com/showthread...hreadid=533684
>

[scantor145]

Still having trouble unfortunately.

The first section of code below "should" set the value of the LotNumber. It then calls the routine at the end of which the User form is shown that displays the Lot Number text box. The first time the program is run there shouldn't be any value. True or False?

Please Login or Register  to view this content.

After some information is entered by the user on this part of the form, instructions are given to open a file. Then another form appears in which the user enters additional information. An OK button is then selected which leads to subsequent code, part of which is shown below where the Lot Number is supposedly saved.

Please Login or Register  to view this content.

The user is then asked to save the workbook, the code for which is shown below:

Please Login or Register  to view this content.

When the user runs the program again (code at top of this diatribe) I still don't see the "previously saved" Lot Number which was purportedly set to MyValue right before the start of the routine.

I did try the following:

In the "Save Workbook" section above I inserted the following before the

Please Login or Register  to view this content.

statement just to see what would be shown.:

frmEasyLyteQC.txtLotNum.Value = MyValue
frmEasyLyteQC.Show

Lo and behold MyValue did appear in the textbox. But that does me no good. I want to see MyValue after restarting the program, not during subsequent rerun of the already running program.

[Bob Phillips]

I have told you twice in this thread that you need to run the code

MyValue = Evaluate(ThisWorkbook.Names("SavedTextBox").RefersTo)

to retrieve the save textbox value into a variable which you can then load
back into the tetxbox. If you don't run this code, MyValue will NEVER have a
value, so it doesn't matter first, second, infinitynth time, it won't work.
Variables at best lats within an Excel session, not over different
sesssions, otherwise there would be no need jump through hoops to save such
variables.

This code

Sub QCTrack()

frmEasyLyteQC.txtLotNum.Value = MyValue
Call QCLevels

End Sub

should be

Sub QCTrack()
MyValue = Evaluate(ThisWorkbook.Names("SavedTextBox").RefersTo)
frmEasyLyteQC.txtLotNum.Value = MyValue
Call QCLevels

End Sub


and this code doesn't need to do any evaluation of the name, it is
irrelevant

ThisWorkbook.Names.Add Name:="SavedTextBox", RefersTo:="=" &
txtLotNum.Value
MyValue = Evaluate(ThisWorkbook.Names("SavedTextBox").RefersTo)

'Clear ALL values

For Each Ctls In frmEasyLyteQC.Controls
If Left(Ctls.Name, 3) = "txt" Then
Ctls.Value = ""
End If
Next Ctls

so should be

ThisWorkbook.Names.Add Name:="SavedTextBox", RefersTo:="=" &
txtLotNum.Value

'Clear ALL values

For Each Ctls In frmEasyLyteQC.Controls
If Left(Ctls.Name, 3) = "txt" Then
Ctls.Value = ""
End If
Next Ctls




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"scantor145" <[email protected]> wrote
in message news:[email protected]...
>
> Still having trouble unfortunately.
>
> The first section of code below "should" set the value of the
> LotNumber. It then calls the routine at the end of which the User form
> is shown that displays the Lot Number text box. The first time the
> program is run there shouldn't be any value. True or False?
>
>
> Code:
> --------------------
> Sub QCTrack()
>
> frmEasyLyteQC.txtLotNum.Value = MyValue
> Call QCLevels
>
> End Sub
> Sub QCLevels()
>
> 'Hides Electrolytes Results, Min and Max range textboxes and entering
RESULTS instructions
> frmEasyLyteQC.fraElectrolytes.Visible = False 'Hide Electrolytes
screen
> frmEasyLyteQC.lblInstruct4.Visible = False 'Hide entering RESULT
instruction 4
> frmEasyLyteQC.lblInstruct6.Visible = False 'Hide entering RESULT
instruction 6
> frmEasyLyteQC.cmdOK.Visible = False 'Hide OK button
> frmEasyLyteQC.cmdCancel.Left = 165 'Center the Close button
> frmEasyLyteQC.txtAnalDate.SetFocus 'Put cursor in Date
textbox
>
>
> frmEasyLyteQC.Show 'Shows EasyLyte
Userform
>
> End Sub
> --------------------
>
>
> After some information is entered by the user on this part of the form,
> instructions are given to open a file. Then another form appears in
> which the user enters additional information. An OK button is then
> selected which leads to subsequent code, part of which is shown below
> where the Lot Number is supposedly saved.
>
>
> Code:
> --------------------
> ThisWorkbook.Names.Add Name:="SavedTextBox", RefersTo:="=" &
txtLotNum.Value
> MyValue = Evaluate(ThisWorkbook.Names("SavedTextBox").RefersTo)
>
> 'Clear ALL values
>
> For Each Ctls In frmEasyLyteQC.Controls
> If Left(Ctls.Name, 3) = "txt" Then
> Ctls.Value = ""
> End If
> Next Ctls
> --------------------
>
>
> The user is then asked to save the workbook, the code for which is
> shown below:
>
>
> Code:
> --------------------
> MsgBox ("Save Workbook")
>
> FilterFileList = "Microsoft Excel Files(*.xls),*.xls"
>
> With Application
> MyNewQCFile = .GetSaveAsFilename(filefilter:=FilterFileList)
> End With
>
> Application.ActiveWorkbook.SaveAs Filename:=MyNewQCFile
>
> frmEasyLyteQC.lblInstruct1.Font.Bold = True
> frmEasyLyteQC.fraLevels.Visible = True 'Upon restart; Show
EasyLyte QC Level choices only
>
> End
> --------------------
>
>
> When the user runs the program again (code at top of this diatribe) I
> still don't see the "previously saved" Lot Number which was purportedly
> set to MyValue right before the start of the routine.
>
> I did try the following:
>
> In the "Save Workbook" section above I inserted the following before
> the
> Code:
> --------------------
> End
> --------------------
> statement just to see what would be shown.:
>
> frmEasyLyteQC.txtLotNum.Value = MyValue
> frmEasyLyteQC.Show
>
> Lo and behold MyValue did appear in the textbox. But that does me no
> good. I want to see MyValue after restarting the program, not during
> subsequent rerun of the already running program.
>
>
> --
> scantor145
> ------------------------------------------------------------------------
> scantor145's Profile:
http://www.excelforum.com/member.php...o&userid=14766
> View this thread: http://www.excelforum.com/showthread...hreadid=533684
>