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