HELP - Creating Folder and Hyperlinking in EXCEL using VBA

[daleseng]

Morning,


I have next to zero VBA training, what i have is a previous colleague had created a worksheet with automatic hyperlinking that created a folder structure within a shared drive.


We are making a new sheet and i'm trying to use the same code for the new sheet. After spending the past few days working on it i think i'm almost there on creating folders, but for some reason the hyper link is not working..


see code below, the error is highlighting the part "ActiveSheet.Hyperlinks.Add Anchor:=Range("B" & i), Address:=dir"






Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim R As Range
Dim RootFolder As String
RootFolder = "S:\1A - Dales OR PMS\1 - Dales OR Estimating"
For Each R In Range("B7:B56")
If Len(R.Text) > 0 Then
On Error Resume Next
MkDir RootFolder & "" & R.Text
MkDir RootFolder & "" & R.Text & "\01 - Clients Docs"
MkDir RootFolder & "" & R.Text & "\02 - DESOR Estimate Docs"
MkDir RootFolder & "" & R.Text & "\03 - Sub-Contractors Docs"
MkDir RootFolder & "" & R.Text & "\04 - Drawings"
MkDir RootFolder & "" & R.Text & "\05 - Technical"
MkDir RootFolder & "" & R.Text & "\06 - Photos"
MkDir RootFolder & "" & R.Text & "\07 - Emails"
MkDir RootFolder & "" & R.Text & "\08 - Material Costs"
On Error GoTo 0
End If
Next R




Dim cnf
Dim dir As String
Dim fnsh As Long
Dim i As Long
Set cnf = CreateObject("Scripting.FileSystemObject")
fnsh = ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
For i = 4 To fnsh
dir = "S:\1A - Dales OR PMS\1 - Dales OR Estimating" & Range("A" & i).Value
If Not cnf.FolderExists(dir) Then
cnf.CreateFolder (dir)
End If
'Range("B" & i).Hyperlinks.Delete
ActiveSheet.Hyperlinks.Add Anchor:=Range("B" & i), Address:=dir
Next
Set cnf = Nothing
Application.ScreenUpdating = True
End Sub


any info at all would be appreciated! this is my first time here so if i have posted this in the wrong place or not included enough information please let me know.


Thanks


Joe