Track changes then call changed value in module

[somethingz]

Hi!
I've searched everywhere (not just this forum) and I can't figure this problem out and therefore I hope someone here can assist

What I want:
I want to be able to create a folder whenever a new entry is made in i.e. column A with the folder name being whatever is entered in the row. I.e. someone types "321" in A1 and a folder is created called A1

What I have:
I have a module which creates the folder (it's on sharepoint which made it even more complicated) but the module needs a foldername, right now it just says newFolderName = "Test".

I want to pass a variable to this from a change in my sheet. I created a worksheet change code which tracks changes. This is where I'm lost. Do I call this worksheet (I've read that it's not possible to call a worksheet change) or can I store the value elsewhere and then call it from my module?

Sub Worksheet_Change(ByVal Target As Range)

Dim rangeToChange As Range

Set rangeToChange = Range("A1:A50")        ' This is just for testing, I want it to be something like from A1 to infinity (or maxRows or however its done)

Dim newFolderName As String

If Not Intersect(Target, rangeToChange) Is Nothing Then
    newFolderName = Target.Value
End If
End Sub

Here is the module that creates the folders:

Public Sub SharepointAddFolder()

    Dim filePath As String
    filePath = "https://COMPANYNAME.sharepoint.com/X/X/X/Where folders are created"    ' This part works fine

    Dim newFolderName As String

    Call Worksheet_Change ' What i want is basically newFolderName = current target value from worksheet change which updates and runs every time worksheet change finds a change
    ' This is what the code already had but obviously this doesn't update: 
    ' newFolderName = "Test"
    Dim driveLetter As String
    driveLetter = "Z:"

    Dim ntwk As Object
    Set ntwk = CreateObject("WScript.Network")

    On Error GoTo ErrHandler
    ntwk.MapNetworkDrive driveLetter, filePath, False ', "username", "password"

    If Len(Dir(driveLetter & "/" & newFolderName, vbDirectory)) = 0 Then
        MkDir driveLetter & "/" & newFolderName
    Else
        MsgBox "Folder " & newFolderName & "already exists."
    End If
End Sub

I'm grateful of any help even just references on what to read up on to fix this!

 

[offthelip]

If I have understood what your problem is I think you want to pass a parameter to your sharepoint add sub routine, you can do it like this:

Sub test()
tt = " My new name"
call SharepointAddFolder (tt)
End Sub


Public Sub SharepointAddFolder(newFolderName As String)

    Dim filePath As String
    filePath = "https://COMPANYNAME.sharepoint.com/X/X/X/Where folders are created"    ' This part works fine

   ' Dim newFolderName As String
    MsgBox newFolderName
  

End Sub

Try running this code exactly as I have sent it and you should see what it does.

 

[somethingz]

If I have understood what your problem is I think you want to pass a parameter to your sharepoint add sub routine, you can do it like this:

Sub test()
tt = " My new name"
call SharepointAddFolder (tt)
End Sub


Public Sub SharepointAddFolder(newFolderName As String)

    Dim filePath As String
    filePath = "https://COMPANYNAME.sharepoint.com/X/X/X/Where folders are created"    ' This part works fine

   ' Dim newFolderName As String
    MsgBox newFolderName
 

End Sub

Try running this code exactly as I have sent it and you should see what it does.

Hi Offthelip, thanks so much for the reply!
I tried your suggestion and I get an error code:
Compile Error:
ByRef argument type mismatch

And it shows the error as being in the Sub test() function, specifically the parameter tt in SharepointAddFolder(tt). I tried fiddling with it and by adding

Sub test()
Dim tt As String ' this part I added
tt = "My new name"
Call SharepointAddFolder(tt)
End Sub

Dim tt As String to Sub test() I got it working.

If this was the intended purpose it now shows a msgbox with the name "My new name" as was written in the tt variable.
The final piece to the puzzle is how I add the value myNewFolder from the worksheet_change to this tt variable so I can pass it to the SharepointAddFolder Module.
Also, can't I just pass the parameter directly from worksheet_change to the SharepointAddFolder as an input to the function or do I have to do it through another module?
Thanks again! Greatly appreciated!

 

[somethingz]

I don't know how to edit a post but I got it working! Thanks so much offthelip for helping me understand how to pass a variable to a module! I ended up fixing it by doing this:

Public Sub Worksheet_Change(ByVal Target As Range)

Dim rangeToChange As Range

Set rangeToChange = Range("A1:A50")

Dim newFolderName As String


If Not Intersect(Target, rangeToChange) Is Nothing Then
    newFolderName = Target.Value
    Call SharepointAddFolder(newFolderName)

End If
End Sub

This then added the value of newFolderName to the module SharepointAddFolder which is exactly what I needed!