Automatically Insert Text Into Macro

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Guest]

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]

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/excel.htm#tutorials

 

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

That worked! Thanks!

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