Use Input Box Value for Macro Excel 2007
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.
I thank you sooo much for your time.
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
I thank you sooo much for your time.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The following should do it:
Chnage line 5 to the following:
Then use find replace to change other 'Range("customerid").Value
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
Then use find replace to change other 'Range("customerid").Value
Premium Content
You need an Expert Office subscription to comment.Start Free Trial