Use Input Box Value for Macro Excel 2007

Anne Troy
Anne Troy used Ask the Experts™
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Cost Accountant
Top Expert 2012
The following should do it:

Chnage line 5 to the following:

Dim myCID As String
Do While Len(myCID) <> 5 Or Not IsNumeric(myCID)
    myCID = InputBox("Enter 5 digit customer ID", "CustomerID")

MkDir "\\CK-FILESERV-P01\Factory\FileCabinet\" & myCID

Open in new window

Then use find replace to change other 'Range("customerid").Value' to 'myCID'
Anne TroyEast Coast Manager



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial