Use Input Box Value for Macro Excel 2007

Anne Troy
Anne Troy used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Cost Accountant
Top Expert 2012
Commented:
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")
Loop

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

Author

Commented:
Perfect. THANKS SO MUCH!

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