easy and cheezy for all you geniuses

[Vato Loco]

I need a workbook to save as filename (cell a3) and automatically sav
(without prompting if possible) when any cell in a4:iv4 is populated
Does anyone know how to do this?

Thank all of you for your help. Vato

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A4:IV4")) Is Nothing Then
ThisWorkbook.Save Filename:=Range("A3").Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Vato Loco]

It is giving me an error concerning the save filename syntax. An
suggestions

 

[Bob Phillips]

Yeah, sorry it should have been SaveAs

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A4:IV4")) Is Nothing Then
ThisWorkbook.SaveAs Filename:=Range("A3").Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Vato,

This is an update on the code but I'm not sure I am properly understanding
what you want, as finding a dynamic last column doesn't add much

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cLastCol As Long
Dim rng As Range

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Project")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) <> 10 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Schedule")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) <> 4 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Budget")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) <> 12 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Resource")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) <> 4 Then
MsgBox "required fields missing"
Cancel = True
End If
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Ignore that one Vato, it was a reply to another question.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Vato,

This is an update on the code but I'm not sure I am properly understanding
what you want, as finding a dynamic last column doesn't add much

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cLastCol As Long
Dim rng As Range

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Project")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) <> 10 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Schedule")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) <> 4 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Budget")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) <> 12 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Resource")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) <> 4 Then
MsgBox "required fields missing"
Cancel = True
End If
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)