Hi
I have this spreadsheet I use to keep track of certain info. With a button it opens a form which then creates a new row and then creates a folder based on value B3 and B8.
What I would like to now is have link created to the folder in the same time as the macro runs? The link should be in B8 where the folder name gets one of its value from. Is this possible?
I´m still learning VBA, so I have had some help with this code.
Thank you
I have this spreadsheet I use to keep track of certain info. With a button it opens a form which then creates a new row and then creates a folder based on value B3 and B8.
What I would like to now is have link created to the folder in the same time as the macro runs? The link should be in B8 where the folder name gets one of its value from. Is this possible?
I´m still learning VBA, so I have had some help with this code.
VBA Code:
Private Sub addProject_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Prewi")
With ws
.Range("A8").EntireRow.Insert shift:=xlDown
With .Range("B8:N8")
.Borders.Weight = xlThin
With .Font
.Size = 11
.Bold = False
End With
End With
.Range("D8").Value = Date
.Range("B8").Value = Me.TextBox1.Value
.Range("C8").Value = Me.TextBox2.Value
.Range("E8").Value = Me.TextBox3.Value
.Range("F8").Value = Me.TextBox4.Value
.Range("L8").Formula = "=(J8-K8)"
With .Range("G8").Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Predicted,In progress,Completed"
.InCellDropdown = True
End With
With .Range("H8").Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Yes,No"
.InCellDropdown = True
End With
With .Range("M8").Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Yes,No"
.InCellDropdown = True
End With
With .Range("N8").Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Yes,No"
.InCellDropdown = True
End With
With Worksheets("Prewi").Rows(8)
.RowHeight = 20
End With
End With
'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox1.SetFocus
Dim wb As Workbook
Dim p As String, NwPath As String
Dim c1 As String, c2 As String
Dim folder As String
Set wb = ThisWorkbook
p = wb.Path & "\"
Set ws = wb.Sheets("Prewi")
With ws
c1 = .Range("B3").Value
c2 = .Range("B8").Value
End With
NwPath = p & c2 & "_" & c1
'check if folder exists, if not then create folder
folder = Dir(NwPath, vbDirectory)
If folder = vbNullString Then
VBA.FileSystem.MkDir (NwPath)
End If
Unload Me
End Sub
Thank you