Results 1 to 8 of 8

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

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    Sunshine Coast, Australia
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,799
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    The command is "MkDir", not "Mk".
    See: https://www.techonthenet.com/excel/formulas/mkdir.php
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Jul 2019
    Location
    Sunshine Coast, Australia
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    
    '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"
    
    
    If Sheets("ProjectRegister").Range("O2") = "yes" Then
    
    
    MkDir "C:\Users\billy\Documents\" & Range("C2") & " - " & Range("G2") & ", " & Range("E2")
    End If
    
    
    End Sub

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,799
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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 by Joe4; Jul 25th, 2019 at 07:45 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Jul 2019
    Location
    Sunshine Coast, Australia
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,799
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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...
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Jul 2019
    Location
    Sunshine Coast, Australia
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 ☺️

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,799
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    Excellent! I am glad I could help.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •