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

[BillyB]

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

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

 

[Joe4]

The command is "MkDir", not "Mk".
See: https://www.techonthenet.com/excel/formulas/mkdir.php

 

[BillyB]

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

 

[Joe4]

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

 

[BillyB]

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

 

[Joe4]

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

 

[BillyB]

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

 

[Joe4]

Excellent! I am glad I could help.