Message Box

[superfb]

Hi

i have a macro code that creates a folder by what i specify in a cell. i woud like this code to incorporate a message box telling me what folder has been created

 Sub MakeSubFolder()

Dim lngCount As Long
lngCount = 2
If Len(Dir(Cells(1, 20), vbDirectory)) = 0 Then
   MkDir Cells(1, 20)
Else
    Do Until Len(Dir(Cells(1, 20) & " - " & lngCount, vbDirectory)) = 0
        lngCount = lngCount + 1
    Loop
   MkDir Cells(1, 20) & " - " & lngCount
End If


' message box
MsgBox "Folder Created", vbOKOnly, "Cars"

Please note i usually create a folder in the following format YYYYMMDD If there is two folders with the same name the following folder creates YYYYMMDD - 2..I would like the message box to say what folder has been created.....

many thanks

 

[Yongle]

Re: Message Box help

try

Sub MakeSubFolder()

Dim lngCount As Long, [COLOR=#006400]Msg As String[/COLOR]
lngCount = 2
If Len(Dir(Cells(1, 20), vbDirectory)) = 0 Then
   MkDir Cells(1, 20)
Else
    Do Until Len(Dir(Cells(1, 20) & " - " & lngCount, vbDirectory)) = 0
        lngCount = lngCount + 1
    Loop
   MkDir Cells(1, 20) & " - " & lngCount
   [COLOR=#006400]Msg = " - " & lngCount[/COLOR]
End If


' message box
MsgBox "Folder Created" [COLOR=#006400]& vbCr & Cells(1, 20) & Msg[/COLOR] , vbOKOnly, "Cars"

 

[superfb]

Re: Message Box help

That is awesome - works a treat! can you explain very kindly the new code added by you what it does???? just so i could have a better understanding

 

[Yongle]

Re: Message Box help

Dim lngCount As Long, Msg As String
lngCount = 2
If Len(Dir(Cells(1, 20), vbDirectory)) = 0 Then
   MkDir Cells(1, 20)
Else
    Do Until Len(Dir(Cells(1, 20) & " - " & lngCount, vbDirectory)) = 0
        lngCount = lngCount + 1
    Loop
   MkDir Cells(1, 20) & " - " & lngCount
   Msg = " - " & lngCount   ([B][I]Note 1[/I][/B])
End If

MsgBox "Folder Created" & vbCr & Cells(1, 20) & Msg , vbOKOnly, "Cars"  ([B][I]Note 2[/I][/B])

Note 1
Msg = whatever the code added to YYYYMMDD when creating the new folder

Note 2
MsgBox "Folder Created" & vbCr & Cells(1, 20) & Msg

& = concatenate (join text together)
vbCr = carriage return (puts text on next line)

If folder YYYYMMDD did not exist then Msg is empty
Cells(1, 20) & Msg returns:
Folder Created
20190325

If folder YYYYMMDD did exist then Msg contains a value
Cells(1, 20) & Msg(=whatever the code added to YYYYMMDD) returns:
Folder Created
20190325 - 2

 

[Yongle]

Re: Message Box help

A tidier way to achieve what you want

Dim lngCount As Long, Msg As String
lngCount = 2
If Len(Dir(Cells(1, 20), vbDirectory)) = 0 Then
   Msg = Cells(1, 20)
   MkDir Msg
Else
   Do Until Len(Dir(Cells(1, 20) & " - " & lngCount, vbDirectory)) = 0
        lngCount = lngCount + 1
   Loop
   Msg = Cells(1, 20) & " - " & lngCount
   MkDir Msg
   
End If

' message box
MsgBox "Folder Created" & vbCr & Msg , vbOKOnly, "Cars"

 

[superfb]

Re: Message Box help

thank you thats great!

 

[superfb]

Re: Message Box help

Hiya is there a way I could incorporate the Text formula Text("YYYYMMDD") in the msgbox .....thanks

 

[Yongle]

Re: Message Box help

Try like this

Msg = Format(Cells(1, 20), "YYYYMMDD")