Hello y'all. Need help with the attached file.
I've seen this tutorial from YT about Global Workbook sharing and applied it to my current project. However, it is not working as intended - atleast on my file. I downloaded the excel file from that tutorial and its working fine - also attached the file here.
On the file Draft 4, entries on the calendar should be entered through M4:M9. After that, the entry would appear on the calendar reflecting the date on M3. As you can see, if you select any of the dates on the calendar, the date on M3 would also change. The LoadDay() macro enable the user to load all entries on each day and show it on column M4:M9 when a day with entry is selected.
What's the issue I am having?
I copied the code from the workbook tutorial and change references to match what I need for my project. According to the tutorial, the code below SHOULD create all folders for the user under "Admin" tab and create a Notepad recording changes for the defined range. In my case, it only creates a folder for the "Current User" instead of creating a folder for ALL user and it does not create any Notepad file for the changes made on the range.
If Not Intersect(Target, Range("M11:M18")) Is Nothing Then
If Target.Count > 1 Then 'Allow user 1 change at a time
Application.ScreenUpdating = False
Application.ScreenUpdating = True
LoadDay
End
End If
If Sheet3.Range("B3").Value = False Then
If Sheet3.Range("SharedFolder").Value = Empty Then End
Dim UserRow As Long
Dim CurrentUser As String
Dim SharedFolder As String
Dim Username As String
Dim FileName As String
Dim fso As Object
Dim oFile As Object
Set fso = CreateObject("Scripting.FileSystemObject")
CurrentUser = Sheet3.Range("CurrentUser").Value 'Current User
SharedFolder = Sheet3.Range("SharedFolder").Value 'Shared Folder
For UserRow = 5 To 19
If Sheet3.Range("D" & UserRow).Value = Empty Then GoTo NoUser
Username = Sheet3.Range("D" & UserRow).Value
If CurrentUser = Username Then GoTo NextUser
If Dir(SharedFolder & "\" & Username & "\", vbDirectory) = "" Then fso.createfolder (SharedFolder & "\" & Username & "\")
FileName = SharedFolder & "\" & Username & "\" & Target.Worksheet.Name & Target.Address & ".txt"
Set oFile = fso.CreateTextFile(FileName)
oFile.writeline Target.Worksheet.Name & "," & Target.Address & ":" & Target.Value
oFile.Close
NextUser:
Next UserRow
Set fso = Nothing
Set oFile = Nothing
NoUser:
End If
End If
Display More
The macro below allow the "Synch" button to run and reflect all changes from the original workbook to the copy workbook.
Sub SynchFile()
Dim FileName As String
Dim FilePath As String
Dim LongFileName As String
Dim fso As Object
Dim oFile As Object
Dim TCFile As String
Dim SyncText As String
Dim CurrentUser As String
Dim SheetName As String
Dim CellAddress As String
Dim CellText As String
Set fso = CreateObject("Scripting.Filesystemobject")
CurrentUser = Sheet3.Range("CurrentUser").Value
If Dir(Sheet3.Range("SharedFolder").Value, vbDirectory) = "" Then Exit Sub
FilePath = Sheet3.Range("SharedFolder").Value & "\" & CurrentUser & "\"
If Dir(FilePath, vbDirectory) = Empty Then
fso.createfolder (FilePath) 'Add User Folder if needed
Exit Sub 'Exit, nothing to synch
End If
Sheet3.Range("B3").Value = True 'Set Syncing to True
FileName = Dir(FilePath & "*.txt")
Do While Len(FileName) > 0 'Start of Loop
LongFileName = FilePath & FileName
Open LongFileName For Input As #1
Line Input #1, SyncText
Close #1
SheetName = Left(SyncText, InStr(SyncText, ",") - 1) 'Sheet Name
CellAddress = Mid(SyncText, InStr(SyncText, ",") + 1, InStr(SyncText, ":") - InStr(SyncText, ",") - 1)
CellText = Right(SyncText, Len(SyncText) - InStr(SyncText, ":"))
ThisWorkbook.Sheets(SheetName).Range(CellAddress).Value = CellText
Kill (LongFileName)
FileName = Dir() 'Clear out Current File name
Loop
Sheet3.Range("B3").Value = False 'Set Syncing to False
Set fso = Nothing
Set oFile = Nothing
End Sub
Display More
I really hope someone could help me figure out what I am doing wrong here. I would really appreciate it.
Initially, I think I have a problem with the range I declared below. But, I am not sure how to move forward.
Thank you so much guys!