Automatically Insert Text Into Macro
 

Is there anyway I can automatically insert text into a macro by referencing
the worksheet and cell where the text can be found? I have done it in some
cases as part of other text in a line in the macro using " &
Sheets("Customize").Range("L45").Value & " but have not been able to figure
out how to insert a whole line. I currently build macros using excel formulas
based on answers to questions (i.e., if the answers is "yes" to the
questions, "Do you want to update links and Do you want to open the file
Read-Only?", my formula produces the text "UpdateLinks:=3, ReadOnly:=True" in
cell L45 of worksheet "Customize" but I still have to manually copy and paste
that into the macro. Thanks.

Automatically Insert Text Into Macro
 

Looks like you are missing the point of how to use variables.

Dim lNum as Long
Dim bVal as Long
Dim ans as Long
ans = Msgbox("Want to update links" vbYesNo)
if ans = vbNo then
lNum = 0
else
lNum = 3
end if
ans = MsgBox("Want to open Read Only",vbYesNo)
if ans = vbYes then
bVal = True
else
bVal = False
End if

workbooks.Open Filename:=sName, _
UdateLinks:=lNum, ReadOnly:=bVal

Otherwise, see Chip Pearson's page on working on code with code
http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy



"Carl Bowman" wrote in message
...
Is there anyway I can automatically insert text into a macro by
referencing
the worksheet and cell where the text can be found? I have done it in some
cases as part of other text in a line in the macro using " &
Sheets("Customize").Range("L45").Value & " but have not been able to
figure
out how to insert a whole line. I currently build macros using excel
formulas
based on answers to questions (i.e., if the answers is "yes" to the
questions, "Do you want to update links and Do you want to open the file
Read-Only?", my formula produces the text "UpdateLinks:=3, ReadOnly:=True"
in
cell L45 of worksheet "Customize" but I still have to manually copy and
paste
that into the macro. Thanks.

Automatically Insert Text Into Macro
 

Thanks, Tom
I think you are correct...I am in the process of realizing macros are just
too limited and am going to have to go through the painful learning curve to
start writing my own code. But, to be honest, I believe you gave me good
advice but it is all Greek to me and I don't know how to even start. How does
your example "know" which cell contains the yes/no answer? What starts this
procedure operating? Please forgive my ignorance. Thanks!
Carl

"Tom Ogilvy" wrote:

Looks like you are missing the point of how to use variables.

Dim lNum as Long
Dim bVal as Long
Dim ans as Long
ans = Msgbox("Want to update links" vbYesNo)
if ans = vbNo then
lNum = 0
else
lNum = 3
end if
ans = MsgBox("Want to open Read Only",vbYesNo)
if ans = vbYes then
bVal = True
else
bVal = False
End if

workbooks.Open Filename:=sName, _
UdateLinks:=lNum, ReadOnly:=bVal

Otherwise, see Chip Pearson's page on working on code with code
http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy



"Carl Bowman" wrote in message
...
Is there anyway I can automatically insert text into a macro by
referencing
the worksheet and cell where the text can be found? I have done it in some
cases as part of other text in a line in the macro using " &
Sheets("Customize").Range("L45").Value & " but have not been able to
figure
out how to insert a whole line. I currently build macros using excel
formulas
based on answers to questions (i.e., if the answers is "yes" to the
questions, "Do you want to update links and Do you want to open the file
Read-Only?", my formula produces the text "UpdateLinks:=3, ReadOnly:=True"
in
cell L45 of worksheet "Customize" but I still have to manually copy and
paste
that into the macro. Thanks.

Automatically Insert Text Into Macro
 

It could be started in several ways. One, like a recorded macro, it could
be started in tools=Macro=Macros by selecting its name a clicking run.
However, this is a code segment. It would have to be enclosed in a routine
like

Sub MyCode()

End Sub

such a sub could also be assigned to a button

The code is stand alone. It puts up a msgbox and asks the user for their
preference for each choice - update links, then read only. As written,
these preferences are not placed in a worksheet, but used immediately to
open a file. I don't know what you are doing, so this may not be
appropriate, but I can't think of anytime I would ever get a string like
"UpdateLinks:=3, ReadOnly:=True"
from a worksheet cell and merge it into a line of code in the vbe.

David McRitchie has some links to tutorials. the VBA tutorials are listed
after the Excel tutorials:

http://www.mvps.org/dmcritchie/excel....htm#tutorials

--
Regards,
Tom Ogilvy

"Carl Bowman" wrote in message
...
Thanks, Tom
I think you are correct...I am in the process of realizing macros are just
too limited and am going to have to go through the painful learning curve
to
start writing my own code. But, to be honest, I believe you gave me good
advice but it is all Greek to me and I don't know how to even start. How
does
your example "know" which cell contains the yes/no answer? What starts
this
procedure operating? Please forgive my ignorance. Thanks!
Carl

"Tom Ogilvy" wrote:

Looks like you are missing the point of how to use variables.

Dim lNum as Long
Dim bVal as Long
Dim ans as Long
ans = Msgbox("Want to update links" vbYesNo)
if ans = vbNo then
lNum = 0
else
lNum = 3
end if
ans = MsgBox("Want to open Read Only",vbYesNo)
if ans = vbYes then
bVal = True
else
bVal = False
End if

workbooks.Open Filename:=sName, _
UdateLinks:=lNum, ReadOnly:=bVal

Otherwise, see Chip Pearson's page on working on code with code
http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy



"Carl Bowman" wrote in message
...
Is there anyway I can automatically insert text into a macro by
referencing
the worksheet and cell where the text can be found? I have done it in
some
cases as part of other text in a line in the macro using " &
Sheets("Customize").Range("L45").Value & " but have not been able to
figure
out how to insert a whole line. I currently build macros using excel
formulas
based on answers to questions (i.e., if the answers is "yes" to the
questions, "Do you want to update links and Do you want to open the
file
Read-Only?", my formula produces the text "UpdateLinks:=3,
ReadOnly:=True"
in
cell L45 of worksheet "Customize" but I still have to manually copy
and
paste
that into the macro. Thanks.

Automatically Insert Text Into Macro
 

Tom,
Thank you so much. You gave me enough information to work with! Thanks again.
Carl

"Carl Bowman" wrote:

Thanks, Tom
I think you are correct...I am in the process of realizing macros are just
too limited and am going to have to go through the painful learning curve to
start writing my own code. But, to be honest, I believe you gave me good
advice but it is all Greek to me and I don't know how to even start. How does
your example "know" which cell contains the yes/no answer? What starts this
procedure operating? Please forgive my ignorance. Thanks!
Carl

"Tom Ogilvy" wrote:

Looks like you are missing the point of how to use variables.

Dim lNum as Long
Dim bVal as Long
Dim ans as Long
ans = Msgbox("Want to update links" vbYesNo)
if ans = vbNo then
lNum = 0
else
lNum = 3
end if
ans = MsgBox("Want to open Read Only",vbYesNo)
if ans = vbYes then
bVal = True
else
bVal = False
End if

workbooks.Open Filename:=sName, _
UdateLinks:=lNum, ReadOnly:=bVal

Otherwise, see Chip Pearson's page on working on code with code
http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy



"Carl Bowman" wrote in message
...
Is there anyway I can automatically insert text into a macro by
referencing
the worksheet and cell where the text can be found? I have done it in some
cases as part of other text in a line in the macro using " &
Sheets("Customize").Range("L45").Value & " but have not been able to
figure
out how to insert a whole line. I currently build macros using excel
formulas
based on answers to questions (i.e., if the answers is "yes" to the
questions, "Do you want to update links and Do you want to open the file
Read-Only?", my formula produces the text "UpdateLinks:=3, ReadOnly:=True"
in
cell L45 of worksheet "Customize" but I still have to manually copy and
paste
that into the macro. Thanks.

Automatically Insert Text Into Macro
 

Thanks, Tom.
If I might impose on your patience one more time...The code you wrote is for
a message box that starts on opening. I need something that responds to the
answer in a cell ("L19") of worksheet ("Customize") asking whether the user
wants to open another file as Read-Only. (I already have the code working to
get the other file to open based on the users answers as to name of file and
path). I tried just replacing your text "ans = Msgbox("Want to update links"
vbYesNo)" with a reference to the worksheet and cell but did not work.
Thanks (I am going to be reading some of the references you suggested).
Carl

"Carl Bowman" wrote:

Tom,
Thank you so much. You gave me enough information to work with! Thanks again.
Carl

"Carl Bowman" wrote:

Thanks, Tom
I think you are correct...I am in the process of realizing macros are just
too limited and am going to have to go through the painful learning curve to
start writing my own code. But, to be honest, I believe you gave me good
advice but it is all Greek to me and I don't know how to even start. How does
your example "know" which cell contains the yes/no answer? What starts this
procedure operating? Please forgive my ignorance. Thanks!
Carl

"Tom Ogilvy" wrote:

Looks like you are missing the point of how to use variables.

Dim lNum as Long
Dim bVal as Long
Dim ans as Long
ans = Msgbox("Want to update links" vbYesNo)
if ans = vbNo then
lNum = 0
else
lNum = 3
end if
ans = MsgBox("Want to open Read Only",vbYesNo)
if ans = vbYes then
bVal = True
else
bVal = False
End if

workbooks.Open Filename:=sName, _
UdateLinks:=lNum, ReadOnly:=bVal

Otherwise, see Chip Pearson's page on working on code with code
http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy



"Carl Bowman" wrote in message
...
Is there anyway I can automatically insert text into a macro by
referencing
the worksheet and cell where the text can be found? I have done it in some
cases as part of other text in a line in the macro using " &
Sheets("Customize").Range("L45").Value & " but have not been able to
figure
out how to insert a whole line. I currently build macros using excel
formulas
based on answers to questions (i.e., if the answers is "yes" to the
questions, "Do you want to update links and Do you want to open the file
Read-Only?", my formula produces the text "UpdateLinks:=3, ReadOnly:=True"
in
cell L45 of worksheet "Customize" but I still have to manually copy and
paste
that into the macro. Thanks.

Automatically Insert Text Into Macro
 

Dim bVal as Boolean
bVal = worksheets("customize").Range("L9").Value
workbooks.Open Filename:=sName, _
UdateLinks:=lNum, ReadOnly:=bVal

--
Regards,
Tom Ogilvy


"Carl Bowman" wrote in message
...
Thanks, Tom.
If I might impose on your patience one more time...The code you wrote is
for
a message box that starts on opening. I need something that responds to
the
answer in a cell ("L19") of worksheet ("Customize") asking whether the
user
wants to open another file as Read-Only. (I already have the code working
to
get the other file to open based on the users answers as to name of file
and
path). I tried just replacing your text "ans = Msgbox("Want to update
links"
vbYesNo)" with a reference to the worksheet and cell but did not work.
Thanks (I am going to be reading some of the references you suggested).
Carl

"Carl Bowman" wrote:

Tom,
Thank you so much. You gave me enough information to work with! Thanks
again.
Carl

"Carl Bowman" wrote:

Thanks, Tom
I think you are correct...I am in the process of realizing macros are
just
too limited and am going to have to go through the painful learning
curve to
start writing my own code. But, to be honest, I believe you gave me
good
advice but it is all Greek to me and I don't know how to even start.
How does
your example "know" which cell contains the yes/no answer? What starts
this
procedure operating? Please forgive my ignorance. Thanks!
Carl

"Tom Ogilvy" wrote:

Looks like you are missing the point of how to use variables.

Dim lNum as Long
Dim bVal as Long
Dim ans as Long
ans = Msgbox("Want to update links" vbYesNo)
if ans = vbNo then
lNum = 0
else
lNum = 3
end if
ans = MsgBox("Want to open Read Only",vbYesNo)
if ans = vbYes then
bVal = True
else
bVal = False
End if

workbooks.Open Filename:=sName, _
UdateLinks:=lNum, ReadOnly:=bVal

Otherwise, see Chip Pearson's page on working on code with code
http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy



"Carl Bowman" wrote in
message
...
Is there anyway I can automatically insert text into a macro by
referencing
the worksheet and cell where the text can be found? I have done it
in some
cases as part of other text in a line in the macro using " &
Sheets("Customize").Range("L45").Value & " but have not been able
to
figure
out how to insert a whole line. I currently build macros using
excel
formulas
based on answers to questions (i.e., if the answers is "yes" to
the
questions, "Do you want to update links and Do you want to open
the file
Read-Only?", my formula produces the text "UpdateLinks:=3,
ReadOnly:=True"
in
cell L45 of worksheet "Customize" but I still have to manually
copy and
paste
that into the macro. Thanks.

Automatically Insert Text Into Macro
 

That worked! Thanks!

"Tom Ogilvy" wrote:

Dim bVal as Boolean
bVal = worksheets("customize").Range("L9").Value
workbooks.Open Filename:=sName, _
UdateLinks:=lNum, ReadOnly:=bVal

--
Regards,
Tom Ogilvy


"Carl Bowman" wrote in message
...
Thanks, Tom.
If I might impose on your patience one more time...The code you wrote is
for
a message box that starts on opening. I need something that responds to
the
answer in a cell ("L19") of worksheet ("Customize") asking whether the
user
wants to open another file as Read-Only. (I already have the code working
to
get the other file to open based on the users answers as to name of file
and
path). I tried just replacing your text "ans = Msgbox("Want to update
links"
vbYesNo)" with a reference to the worksheet and cell but did not work.
Thanks (I am going to be reading some of the references you suggested).
Carl

"Carl Bowman" wrote:

Tom,
Thank you so much. You gave me enough information to work with! Thanks
again.
Carl

"Carl Bowman" wrote:

Thanks, Tom
I think you are correct...I am in the process of realizing macros are
just
too limited and am going to have to go through the painful learning
curve to
start writing my own code. But, to be honest, I believe you gave me
good
advice but it is all Greek to me and I don't know how to even start.
How does
your example "know" which cell contains the yes/no answer? What starts
this
procedure operating? Please forgive my ignorance. Thanks!
Carl

"Tom Ogilvy" wrote:

Looks like you are missing the point of how to use variables.

Dim lNum as Long
Dim bVal as Long
Dim ans as Long
ans = Msgbox("Want to update links" vbYesNo)
if ans = vbNo then
lNum = 0
else
lNum = 3
end if
ans = MsgBox("Want to open Read Only",vbYesNo)
if ans = vbYes then
bVal = True
else
bVal = False
End if

workbooks.Open Filename:=sName, _
UdateLinks:=lNum, ReadOnly:=bVal

Otherwise, see Chip Pearson's page on working on code with code
http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy



"Carl Bowman" wrote in
message
...
Is there anyway I can automatically insert text into a macro by
referencing
the worksheet and cell where the text can be found? I have done it
in some
cases as part of other text in a line in the macro using " &
Sheets("Customize").Range("L45").Value & " but have not been able
to
figure
out how to insert a whole line. I currently build macros using
excel
formulas
based on answers to questions (i.e., if the answers is "yes" to
the
questions, "Do you want to update links and Do you want to open
the file
Read-Only?", my formula produces the text "UpdateLinks:=3,
ReadOnly:=True"
in
cell L45 of worksheet "Customize" but I still have to manually
copy and
paste
that into the macro. Thanks.