VBA code to auto-fill input box in excel 2010

[kanddo2]

Hi,

I have macro I have written that once clicked on from an object pulls certain named tabs from a file, paste them as values, and saves that file outside of the existing workbook they came from. What I am trying to do is auto-fill the input box prompt with a specific naming sequence instead of describing it in the actual box for someone to type in.

I specifically want to input the dialog box with a specific formatted name, but don't want to automatically have the macro save it as that name, without the user checking it or being able to adjust it when necessary. I can seem to figure out the code to provide a blank input box, with description or naming convention, or the code that automatically save it based on a cell that follows that convention. However I want to get to that in between point, when the user can review the name first before saving. I know I am missing something in this attempt. I am just stuck on finding that middle ground.

Any help would be appreciated, code below is what I am using for the free form input box. I would like to keep that box, and just auto-fill that with a specific naming convention. I have a cell where is automatically does that on a tab named "Opportunity", it is cell D17.

' Input box to name new file
NewName = InputBox("Name of your New DGR Packet Workbook. " & vbCr & _
"Recommended Format:" & vbCr & _
" " & vbCr & _
"<"Co.Name"<co. name=""><co. name<client="" name="">> - <"Pursuit Type">_<"Tracking #"<sfdc#><sfdc#<sfdc#>>_SPT DGR Packet_YYYY.MM.DD_v#.##" & vbCr & _
" " & vbCr & _
"EX: ZYX Inc - Marketing Campaign_4321234_SPT DGR Packet_2015.05.25_v1.01", "New Copy")

' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xlsm"
ActiveWorkbook.Close SaveChanges:=False</sfdc#<sfdc#></sfdc#></co.></co.>

 

[Andrew Poulsom]

You can use the InputBox's Default argument. It's the third. Currently you are only using the first two (Prompt and Title).

NewName = InputBox("Name of your New DGR Packet Workbook. " & vbCr & _
"Recommended Format:" & vbCr & _
" " & vbCr & _
"<<CLIENT name="">> - <
>_<<SFDC#>>_SPT DGR Packet_YYYY.MM.DD_v#.##" & vbCr & _
" " & vbCr & _
"EX: ZYX Inc - Marketing Campaign_4321234_SPT DGR Packet_2015.05.25_v1.01", "New Copy", Worksheets("Opportunity").Range("D17").Value)

 

[kanddo2]

So I tired that before and again here thinking I typed something incorrectly. The result is I get a runtime error '9" message followed by Subscript out of range.

 

[kanddo2]

So I tired that before and again here thinking I typed something incorrectly. The result is I get a runtime error '9" message followed by Subscript out of range.

 

[Andrew Poulsom]

Are you sure that you have a worksheet named Opportunity in the active workbook?

 

[kanddo2]

Positive. It is one of the sheets being pulled and values pasted into the file to be named by the Input Box.

But, I just found a typo I did at the range reference. I think I made the same mistake earlier. Stupid fingers anyways. That fixed the issue and the prompt works now as I wanted it to.

Appreciate the help with making the reference work properly. One simple missed keystoke was the issue.