VBA, insert file name into input box

[diatex]

HI
Iam using the following code kindly supplied by "Tactps" to display an "input box" in which the the file name is entered.

FileName1 = InputBox("Please input filename", "Filename")
ChDir "c:\"
ActiveWorkbook.SaveAs Filename:="C:\" & FileName1, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Instead of manually entering the filename i was hoping to pick the filename up from a cell (G3) so that it would appear in the "input box" purely for visible confirmation which can be OK ed. I have been playing around with the following code adding it to the code above without a glimmer of success.

Activeworkbook.SaveAs Filename:=range("G3").value

Although this line saves the file as "G3" it does so without any visual indication or conformation. Any help to fill the "input box would be much appreciated.

Tryed and failed once again

Diatex

 

[DRJ]

Try this

x = InputBox("Prompt", "Title", "Default Value")

Or in your case

FileName1 = InputBox("Please input filename", "Filename", Range("G3").Value)

 

[diatex]

DRJ to the rescue again,
held off with the post until i recognised a few familiar names.

Just what i wanted, easy when you know how and amazing when you don't.

Thanks again

Diatex

 

[DRJ]

Glad to help

Take Care

 

[TommyGun]

Another method to the madness...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SaveFile()
    <SPAN style="color:#00007F">Dim</SPAN> FileName1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> vbAns <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    FileName1 = Range("G3").Value
    
    vbAns = MsgBox("Do you want to save as:" & vbNewLine & vbNewLine & FileName1, _
        vbYesNoCancel + vbInformation, "Save File")
        
    <SPAN style="color:#00007F">If</SPAN> vbAns = vbYes <SPAN style="color:#00007F">Then</SPAN>
        ActiveWorkbook.SaveAs FileName1
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[diatex]

Thanks TommyGun,
like the look of this.
How would you tweak this to specify the route to the required save location. At the moment it will only save to the last saved location i.e. if i use file\ save as and choose a different folder the next time i use this code it saves in the same place.

sorry for the delay in replying, but i have been playing around trying to work it out for myself (to no avail)

Thanks
Diatex

Sorted it added the line: ChDir "c:\LAC\"

 

[hangten4881]

Hi,

I am trying to create a simple Macro to allow the user to input a specific row number in a input box. The idea is to allow the macro to select a specific row to a set another set row. Below is the example of what I am working on right now. The Macro below keeps giving me a runtime error, "RUN-time error '1004."

Please help. I am trying to help veterans!!!

----------------------------------------------------------

Contention = InputBox ("Please type in row number of the cell that is highlighted", "Please type in row")

contention = "A" + contention

Range ("contention:O9999").Select

Selection.Copy

 

[John_w]

To fix the error, change:

Range ("contention:O9999").Select

To:

Range(contention & ":O9999").Select


There's no need for a separate Select and Copy, so you could use:

Range(contention & ":O9999").Copy

Also, if by highlighted cell you mean the active cell, you could delete the InputBox line and use:

contention = "A" & ActiveCell.Row

 

[hangten4881]

Thanks so much!!!

Any books or advise to help me learn more about VBA?

 

[jeffreybrown]

A couple by John Wallenbach are highly recommeded.

Excel 2007 VBA for Dummies
and
Microsoft Excel 2007 Power Programming with VBA