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

BillyB

New Member
Joined
Jul 25, 2019
Messages
6
Hi,

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):

Code:
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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:

Code:
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
 
Upvote 0
I would just use an Input Box, i.e.
Code:
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:
Upvote 0
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:

VALUE INPUT BY USER = "190055" for EXAMPLE

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?

then


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
 
Upvote 0
Try this:
Code:
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)
err_chk:
    If Err.Number = 91 Then
        MsgBox "Cannot find " & lookup & " in column C of ProjectRegister sheet", vbOKOnly, "ERROR!"
    Else
        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:
"C&i"
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:
C:\Users\billy\Documents190055...
and not
C:\Users\billy\Documents\190055...
 
Upvote 0
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 ☺️
 
Upvote 0
Excellent! I am glad I could help.
:)
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top