Avatar of Anne Troy
Anne Troy
Flag for United States of America asked on

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.

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.
Microsoft Excel

Avatar of undefined
Last Comment
Anne Troy

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Anne Troy

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck