We help IT Professionals succeed at work.
Get Started

Use Input Box Value for Macro Excel 2007

Anne Troy
Anne Troy asked
Last Modified: 2014-05-02
I have this GREAT macro that saves my workbook and creates a bunch of folders for an order. Now, I want to others to be able to use that macro without saving the file (it'll just be a utility file) and just creates the folders.

It currently uses a named range called customerid. I want the user to be prompted to enter that customerid instead of having a named range for it. The customer ID is a 5-digit number. No letters or symbols. I do NOT want the user to be able to enter 1234 and get folder 01234. I don't need help changing the rest of the code...only how to get that number as the customerid part of the code below.

I could probably figure this out in a few hours (or days...), and I did look online for an example, but everyone wants to show us how to save the input value to a cell, not how to use it later in a macro.

Sub SaveBook()
'Saves the workbook
If Range("checksave").Value = 3 Then
On Error Resume Next
MkDir "\\CK-FILESERV-P01\Factory\FileCabinet\" & Range("customerid").Value
On Error GoTo 0
ActiveWorkbook.SaveAs Filename:=Range("path").Text & Range("filename").Text

On Error Resume Next

MkDir "\\CK-FILESERV-P01\Factory\FileCabinet\" & Range("customerid").Value & "\" & "1. Order Admin"
MkDir "\\CK-FILESERV-P01\Factory\FileCabinet\" & Range("customerid").Value & "\" & "2. Programming"
MkDir "\\CK-FILESERV-P01\Factory\FileCabinet\" & Range("customerid").Value & "\" & "3. Integrations"
MkDir "\\CK-FILESERV-P01\Factory\FileCabinet\" & Range("customerid").Value & "\" & "4. Installation"
MkDir "\\CK-FILESERV-P01\Factory\FileCabinet\" & Range("customerid").Value & "\" & "4. Installation" & "\" & "1Pre"
MkDir "\\CK-FILESERV-P01\Factory\FileCabinet\" & Range("customerid").Value & "\" & "4. Installation" & "\" & "2Post"

Worksheets("Dashboard").Shapes("Rounded Rectangle 6").Delete
Else: MsgBox ("You must enter a MID and Customer ID and Business Name before saving.")

Range("created").Value = Date

End If
ThisWorkbook.FollowHyperlink Address:="\\CK-FILESERV-P01\Factory\FileCabinet\\" & Range("customerid").Value
Exit Sub
End Sub

Open in new window

I thank you sooo much for your time.
Watch Question
Cost Accountant
Top Expert 2012
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE