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

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").Ref ersTo)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"scantor145" wrote
in message ...

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

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

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

Looks like a space has crept in

myValue = Evaluate(ThisWorkbook).Names("__SavedTextBox").Ref ersTo

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"scantor145" wrote
in message ...

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

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").Refer sTo
--------------------


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

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").Refe rsTo)

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").Refer sTo
--------------------

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

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" wrote
in message ...

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").Refer sTo
--------------------


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

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").Refers To)

'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

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

MyValue = Evaluate(ThisWorkbook.Names("SavedTextBox").Refers To)

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").Refers To)
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").Refers To)

'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" wrote
in message ...

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").Refers To)

'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