I was working last night on a Before Save question see here
Click here for the Original Question
I decided to take things further and show a slightly different approach which is my style of programming, I am convinced there are literally thousands of ways to go about these tasks, and improvements can be made in many directions in this example. However I hope that if You read and test and play with my work You can open up ideas and methods that maybe You might not in general usage consider, thus I decided to place the procedures in ‘Hey that’s Cool’ so everyone can look and maybe try and expand on the ideas I have started.
The idea was to test a cell in this case cell B25 and see if it had a DataStream or was in fact empty, I instantly though ah ah ! I need a function as a function freak I went to work. The BeforeSave event needs a few tricks and this is what I came up with, You decide as I say this could be take much further.
Place this code in the ThisWorkSheet Module:
[vba]
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'written by: Jack in the UK - for http://www.OzGrid.com
'www.excel-it.com
'Excel Xp+
Call mySets
Cancel = True
If TestB25(rTarget) = False Then
Cancel = True
Call myInput
Cancel = False
Else
Cancel = False
End If
End Sub[/vba]
Now copy this code to a standard module (this are Your Variables)
[vba]
Option Explicit
Public rTarget As Excel.Range
Public newTargetValue As Variant[/vba]
Add all these procedures uner the variables in the same module
[vba]
Sub myInput()
'written by: Jack in the UK - for http://www.OzGrid.com
'www.excel-it.com
'Excel Xp+
Call mySets
newTargetValue = InputBox("Enter a value")
rTarget.Value = newTargetValue
End Sub[/vba]
[vba]
Public Function TestB25(rTarget As Excel.Range) As Boolean
'written by: Jack in the UK - for http://www.OzGrid.com
'www.excel-it.com
'Excel Xp+
TestB25 = False
Select Case rTarget.Value
Case Is = ""
TestB25 = False
Case Is <> ""
TestB25 = True
Case Else
End Select
Call myKillVals
End Function
[/vba]
[vba]
Sub mySets()
'written by: Jack in the UK - for http://www.OzGrid.com
'www.excel-it.com
'Excel Xp+
Set rTarget = [b25]
End Sub
[/vba]
[vba]
Sub myKillVals()
'written by: Jack in the UK - for http://www.OzGrid.com
'www.excel-it.com
'Excel Xp+
Set rTarget = Nothing
End Sub
[/vba]