VBA code to auto-fill input box in excel 2010

kanddo2

New Member
Joined
Aug 20, 2008
Messages
29
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.>
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You can use the InputBox's Default argument. It's the third. Currently you are only using the first two (Prompt and Title).

Rich (BB code):
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top