Save Workbook For Each Change Made In A Range

[Stalker]

Good afternoon all,

My first post asking for help!
I am trying to create a spreadsheet, with some VBA code.

I would like two codes (so i can see which works better in the intended environment)

1) Once a change is made in Column C - Save the workbook (dont show me any dialogue boxes etc)
2) Once a cell is changed in Column C from blank to non blank - Save the workbook (dont show me any dialogue boxes etc)

I have some experience with VBA coding, but by no means a guru lol.

I then have another sheet that reads from this one. (shared drive locations at work) with coding set up to update/refresh values every minute.

Kind Regards

Stalker

[Excel Fox]

Use this in the code module of the specific sheet where you make changes


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("C:C")) Is Nothing Then
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
End If

End Sub

[Stalker]

Hi Excel Fox,

I already have one code on that sheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range
Dim myPassword As String
myPassword = "UnknowN"
For Each Cell In ActiveSheet.UsedRange
On Error Resume Next
MyCase = UCase(Cell)
If MyCase = "TARGUS" Or MyCase = "YELLOW" Or MyCase = "RAIL" Or MyCase = "IONS" Or MyCase = "EONS" Or MyCase = "WEBSTORE" Or MyCase = "TV" Or MyCase = "CAPTIAL RENT" Or MyCase = "QUICK" Or MyCase = "LIONS" Or MyCase = "GAME" Or MyCase = "DIRECT" Or MyCase = "NEST" Or MyCase = "HELPER" Or MyCase = "OTTER" Then ' add more if needed
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
ActiveSheet.Protect Password:=myPassword ' Password you have to change what you want
MsgBox "Incorrect Entry" & vbCr & vbCr & "Please use the correct sheet", vbCritical
ThisWorkbook.Close savechanges:=True
End If
Next
End Sub

And when i add yours underneath i get the error 'Ambigious name' ggrr

[Stalker]

Apologies, just realised the code is added into my existing, not as a new code :sleepy:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range
Dim myPassword As String
myPassword = "UnknowN"
For Each Cell In ActiveSheet.UsedRange
On Error Resume Next
MyCase = UCase(Cell)
If MyCase = "TARGUS" Or MyCase = "YELLOW" Or MyCase = "RAIL" Or MyCase = "IONS" Or MyCase = "EONS" Or MyCase = "WEBSTORE" Or MyCase = "TV" Or MyCase = "CAPTIAL RENT" Or MyCase = "QUICK" Or MyCase = "LIONS" Or MyCase = "GAME" Or MyCase = "DIRECT" Or MyCase = "NEST" Or MyCase = "HELPER" Or MyCase = "OTTER" Then ' add more if needed
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
ActiveSheet.Protect Password:=myPassword ' Password you have to change what you want
MsgBox "Incorrect Entry" & vbCr & vbCr & "Please use the correct sheet", vbCritical
ThisWorkbook.Close savechanges:=True
End If
Next
If Not Application.Intersect(Target, Range("C:C")) Is Nothing Then
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
End If
End Sub

[Excel Fox]

Well apparently, mine was a mistake. It should actually be a Change event, not a SelectionChange event. You don't want the code to fire when there's a change in the selection, but when you change a value in a selection. I've corrected the code above. Use it, and don't use it within your SelectionChange event.