I am using the folder picker code from John Walken's site. I have a
Sub that brings up the folder picker dialog box. I assigned a string
variable to the selected folder name. After I ran the code, select the
folder and click Ok, the folder value is stored in the string variable.
When I ran the code again (after some procedures took place), before I
selected the folder, the string variable still had the value from the
prior selection. Does any one know how to get ride of the buffered
value without setting the string variable to a blank value. Just
curiours.

- Excel 009

Did you declare the variable locally or globally? A local variable s/b
destroyed when the sub terminates, global will not (which is a the primary
reason for using it). One other means to make a local variable retain it's
value after the sub terminates is to declare the variable as static (just an
FYI - I doubt that is the case).

Global:
Dim x As String
Sub Test
...
End Sub

Local
Sub Test
Dim x As String
...
End Sub

Static:
Sub Test
Static x As String
....
End Sub

"Excel 009" wrote:

I am using the folder picker code from John Walken's site. I have a
Sub that brings up the folder picker dialog box. I assigned a string
variable to the selected folder name. After I ran the code, select the
folder and click Ok, the folder value is stored in the string variable.
When I ran the code again (after some procedures took place), before I
selected the folder, the string variable still had the value from the
prior selection. Does any one know how to get ride of the buffered
value without setting the string variable to a blank value. Just
curiours.

- Excel 009

Funny thing is the value of the variable was still there after the sub
is terminated.

Put the code into a module (code can be found in this link)
http://j-walk.com/ss/excel/tips/tip29.htm

assign the folder value to strX

call up the dialog box and select a folder and click Ok.

Type the following into your model.

Sub Test()
MsgBox strX
End Sub

run the sub, and strX still shows up!

I guess it may have something to do with the API, but not quite sure.
If anyone has the answer, please share.

- Excel 009

If you declared strX at the top of the module above an procedures, then it
is a public variabe or module level variable and will retain its value. If
instead, you declare inside the procedure, then the next time the procedure
is run it will have lost its value.

Other that, we would have to see the specific code you are running. I have
run John's code many times in the past and have had no problems or
surprises. It acted as I suspected.

--
Regards,
Tom Ogilvy


"Excel 009" wrote in message
oups.com...
Funny thing is the value of the variable was still there after the sub
is terminated.

Put the code into a module (code can be found in this link)
http://j-walk.com/ss/excel/tips/tip29.htm

assign the folder value to strX

call up the dialog box and select a folder and click Ok.

Type the following into your model.

Sub Test()
MsgBox strX
End Sub

run the sub, and strX still shows up!

I guess it may have something to do with the API, but not quite sure.
If anyone has the answer, please share.

- Excel 009

The variable IS a public variable, but what I am curious to know is
that when will this variable be destroyed? It seems like once I
assigned value to the variable, it is there even after I run many Sub
manually. When I call up the folder picker dialog box the second time,
if I click Ok without selecting a folder, the old value shows up. I
cleared the clipboard, and it is still there.

(There is nothing wrong with John's code. I appreciate his sharing.)

I've also used that code from John's site w/o any surprises. John's macro,
however, does not store the return value into a variable, it only displays a
message box

Sub Test()
Dim Msg As String
Msg = "Please select a location for the backup."
MsgBox GetDirectory(Msg)
End Sub

whereas you are assigning it to the variable strx. The issue, I'm sure, is
not w/John's code or the API, but how you declared strx - Procedure Level or
Module Level? (inside of your sub or outside of your sub - see my first post
for examples).


"Excel 009" wrote:

Funny thing is the value of the variable was still there after the sub
is terminated.

Put the code into a module (code can be found in this link)
http://j-walk.com/ss/excel/tips/tip29.htm

assign the folder value to strX

call up the dialog box and select a folder and click Ok.

Type the following into your model.

Sub Test()
MsgBox strX
End Sub

run the sub, and strX still shows up!

I guess it may have something to do with the API, but not quite sure.
If anyone has the answer, please share.

- Excel 009

Hit the post button a little to quick, I wanted to add another example. Try
this

Sub Test()
Dim Msg As String
Dim strX As String
Msg = "Please select a location for the backup."
MsgBox strX
strX = GetDirectory(Msg)
MsgBox strX
End Sub



"JMB" wrote:

I've also used that code from John's site w/o any surprises. John's macro,
however, does not store the return value into a variable, it only displays a
message box

Sub Test()
Dim Msg As String
Msg = "Please select a location for the backup."
MsgBox GetDirectory(Msg)
End Sub

whereas you are assigning it to the variable strx. The issue, I'm sure, is
not w/John's code or the API, but how you declared strx - Procedure Level or
Module Level? (inside of your sub or outside of your sub - see my first post
for examples).


"Excel 009" wrote:

Funny thing is the value of the variable was still there after the sub
is terminated.

Put the code into a module (code can be found in this link)
http://j-walk.com/ss/excel/tips/tip29.htm

assign the folder value to strX

call up the dialog box and select a folder and click Ok.

Type the following into your model.

Sub Test()
MsgBox strX
End Sub

run the sub, and strX still shows up!

I guess it may have something to do with the API, but not quite sure.
If anyone has the answer, please share.

- Excel 009

Thanks J.

This is the format I have:

Book1
===================
Dim strX As String

Sub Test()
strX = GetDirectory(Msg)
End Sub


Sub Test_2()
MsgBox strX
End Sub
===================

Book2
===================
Sub Text _3()
Msgbox "Hi"
End Sub

After I ran Test(), I ran Sub Text _3() in workbook 2, then I came back
to workbook 1, ran Sub Test_2(), and the value stored in StrX is still
there. May be my real question is - how the value is preserved in a
way that when you leave the current workbook and come back, it is still
in the memory?

Thanks J.

This is the format I have:

Book1
===================
Dim strX As String

Sub Test()
strX = GetDirectory(Msg)
End Sub


Sub Test_2()
MsgBox strX
End Sub
===================

Book2
===================
Sub Text _3()
Msgbox "Hi"
End Sub

After I ran Test(), I ran Sub Text _3() in workbook 2, then I came back
to workbook 1, ran Sub Test_2(), and the value stored in StrX is still
there. May be my real question is - how the value is preserved in a
way that when you leave the current workbook and come back, it is still
in the memory?