VBA to create folder if a cell value = yes in background


New Member

I'm completely lost where to start with this one.

I have a table of data (Column A = Project Number, B = Name, C = Address, etc) then at the end of the table in Column O, i have "Make folders?".

I am trying to write a piece of code that when the user marks that cell with "yes", a command button can be clicked to make a folder with the values of several of the columns.

Ideally, when the button is pushed/macro started, a messagebox would pop up and ask the user to give the project number (Column A) to identify which row of data is to be used.

so far I have this (which is not working):

Sub CreateFolder()Dim i As Integer

If Range("O2") = "yes" Then

Mk "C:\Users\x\Documents\" & Range("C2") & " - " & Range("G2") & ", " & Range("E2")
End If

End Sub


New Member
Thanks Joe,

I now have code that works! I just need help setting my code up to ask the user which line row of data are they referring to..

I'm thinking that it would work like the following:

Sub CreateFolder()Dim i As Integer

[COLOR=#ff0000]'Show Userform x

'then a lookup formula to find their formula and check if the corresponding value in Column O is marked as "yes" then do the below otherwise pop up with error saying "project doesn't exist"[/COLOR]

If Sheets("ProjectRegister").Range("O2") = "yes" Then

MkDir "C:\Users\billy\Documents\" & Range("C2") & " - " & Range("G2") & ", " & Range("E2")
End If

End Sub


MrExcel MVP, Junior Admin
I would just use an Input Box, i.e.
Dim r As Long
r = InputBox("What row?")

If (Not IsNumeric(r)) Or (r < 1) Then
    MsgBox "You have not entered a valid row number.  Please try again.", vbOKOnly
    Exit Sub
End If

'Rest of code below...
Last edited:


New Member
Thanks Joe that works great but I am trying to do it with a userform now (so i can add some other features)..

I'm a bit stuck with making the code generic.

I have a Userform with a textbox and cmdbutton.

The user inputs a value in the textbox which should be equal to only one unique value in column B..

I want the code to lookup the textbox value against column b and then use that line if possible?

I'm thinking something along the lines of:


Private Sub CommandButton1_Click()

Dim i As Integer

Lookup Textbox.1.Value in Sheets("ProjectRegister").Range(C2:CMAX)

If found set i = rowcount of above and IF NOT FOUND SAY NOT FOUD?


If Sheets("ProjectRegister").Range("O&i") = "yes" Then

MkDir "C:\Users\billy\Documents" & Range("C&i") & " - " & Range("G&i") & ", " & Range("E&i")
End If
End If

End Sub


MrExcel MVP, Junior Admin
Try this:
Private Sub CommandButton1_Click()

    Dim lookup As Variant
    Dim rw As Long
'   Get value from text box to look up
    lookup = Me.TextBox1.Value
'   Find lookup value in column C of ProjectRegister sheet
    On Error GoTo err_chk
    rw = Sheets("ProjectRegister").Columns("C:C").Find(What:=lookup, After:=Sheets("ProjectRegister").Range("C1"), LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Row
    On Error GoTo 0
'   Check column O of found row to see if it is set to "yes"
    If Sheets("ProjectRegister").Range("O" & rw) = "yes" Then
'       Make directory
        MkDir "C:\Users\billy\Documents\" & Range("C" & rw) & " - " & Range("G" & rw) & ", " & Range("E" & rw)
    End If
    Exit Sub

'   Error handling if cannot find value (or other errors)
    If Err.Number = 91 Then
        MsgBox "Cannot find " & lookup & " in column C of ProjectRegister sheet", vbOKOnly, "ERROR!"
        MsgBox Err.Number & ": " & Err.Description
    End If
End Sub
A few notes/explanations.

1. In VBA, anything enclosed in double-quotes is treated as literal text. Any references to variables need to happen OUTSIDE of the double-quotes. You can sew literal text and variables together using &. So to combine literal text to a variable, your would do it like this:
"literal text" & variable

So the range references you are building are incorrect if written like this:
that would be looking for the literal value "C&i", which is not a valid range reference.
It needs to look like:
"C" & i

2. In building your file path, don't forget the last slash after Documents! Otherwise, if the value in column C was 190055, the filepath you are building will be:
and not


New Member
Thank you very much Joe. I really appreciate the time you’ve taken to explain the reasoning behind the code as well. It makes a lot of sense as soon as you explain it.

Again, thank you. This solves the original question ☺

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...