Variable doesn't seem to work

I'm having some trouble with the below bit of code. It's the first part of trying to make sure that users don't overwrite changes in a shared sheet. The next step is to check the value of the variable against the cell it has just been entered into, to see whether another user has already typed something into that cell. If someone has, it will move down the column to the next blank cell, enter the variable into that cell, and then attempt to save again. Hacky I know, but it was the best I could come up with.

The problem is that the variable doesn't seem to be holding the value of the cell. I'm trying to get it to display into a MsgBox just so I can see if it's actually doing something. At the minute the MsgBox is blank.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim inputcellvalue As String
If ActiveCell.Column = 5 Then
inputcellvalue = ActiveCell.Value
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs , , , , , , , ConflictResolution:=xlOtherSessionChanges
ActiveCell.Offset(-1, 0).Select
MsgBox inputcellvalue

Else: End If
    
End Sub

Any ideas?

Hi davepoth,

try the code below - I tried to explain it on the go

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icv$

'If the change in any other column than column E, then exit sub
If Target.Column <> 5 Then Exit Sub

'Prevent the worksheet_change sub from firing itself over and over again :)
Application.EnableEvents = False

'If more than one cell at the same time is altered, undo the change and exit the sub.
If Target.Count > 1 Then Application.Undo: Application.EnableEvents = True: Exit Sub


'Turn of screenupdating for smoother and faster working
Application.ScreenUpdating = False

'Get the new value
icv = Target.Value

'Undo the change
Application.Undo

'Check if the cell was empty before the change - if so, put the value back into the cell, else put it at the bottom of the column
If Target.Value <> "" Then Cells(Rows.Count, Target.Column).End(xlUp).Offset(1, 0).Value = icv Else Target.Value = icv
Application.ScreenUpdating = True

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs , , , , , , , ConflictResolution:=xlOtherSessionChanges

Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Please click the * below if this helps

Glad to hear it helped - please mark the thread as solved
(Thread tools at the top, mark as solved)