easy and cheezy for all you geniuses

V

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
 
B

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)
 
B

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)
 
B

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)
 
B

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)

Bob Phillips said:
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)
Click to expand...
 
Top