Excel VBA help

A[L]C · 27 Sep 2006 at 09:45

Hi all,

Is there any way I set use VBA to validate a worksheet using named ranges.

Ie if i have a block of 4 cells from A1 to B2, what vba code would I need to say

If all cells in the named range are blank then
tell the user they're rubbish and cant fill forms in correctly
End if

??

Any help will be greatly appreciated.
Thanks

A[L]C · 27 Sep 2006 at 14:38

To expand, Im basically doing a form which will use lotus notes to send the current worksheet via email to the helpdesk. Ive built the code which will send the email with the attachement, but want it to validate the form first.

I plan to have a lot of named ranges ie firstname, surname, requiredprinters etc, and want the code to say (psuedo code)

if firstname.value="" then
msgbox "You must enter a value for First Name"
Elseif surname.value - "" then
msgbox "You must enter a value for SurName"
End if

etc. The problem is also that the named range may be more than just one cell, so it would need to say

if all cells in requiredprinters = ""
msgbox "You must enter at least one printer"
End if

Does that explain it a bit better?

A[L]C · 27 Sep 2006 at 16:38

Hi ya,

Thanks for your help. The named range will return a value of the range ie "$A$1:$B$2". How do I get vba to look at each cell in that range, and return an error if they are all blank?

A[L]C · 28 Sep 2006 at 10:57

Ta... the range I get back is "$A$1:$B$2" this doesnt work in the code above. How would I strip any $ signs out of the variable?

A[L]C · 28 Sep 2006 at 11:33

ok... getting there...

strInput = ThisWorkbook.Names("Printers").Value

Test:
If InStr(strInput, "$") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "$") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "$"))
GoTo Test
End If

That gives me =Sheet1!A1:B2

I now need to strip the = out, and split the variable in two so the first is the sheet ie anything up to the ! and the second is the range ie anything after the !. Can anyone help?

A[L]C · 28 Sep 2006 at 11:51

ok made it a bit nicer now:

strInput = ThisWorkbook.Names("Printers").Value
strInput = Replace(strInput, "$", "")
strInput = Replace(strInput, "=", "")
Sheet = InStr(1, strInput, "!")

NameRangeSheet = Left(strInput, Sheet - 1)
NameRangeCells = Right(strInput, Sheet)

A[L]C · 28 Sep 2006 at 12:26

Code:

Dim myRange As Range
Dim NameRangeCells As String
Dim NameRangeSheet As String
   
'Get the range of the named range called Printers
strInput = ThisWorkbook.Names("Printers").Value

'Stip out the $ and = from the range
strInput = Replace(strInput, "=", "")
strInput = Replace(strInput, "$", "")

'Find the position of the !
Sheet = InStr(1, strInput, "!")
   
NameRangeSheet = Left(strInput, Sheet - 1)
NameRangeCells = Right(strInput, Sheet)

Basically im searching a named range to check that a user has entered data in there.

I could use the CountA function if I can work out how to use it with the above.

ATM: I have a named range called Printers set up on D21

24. The above returns the NameRangeSheet as "Sheet1" and the NameRangeCells as "D21

24".

Currently fiddling with:

Code:

Set myRange = Worksheets(NameRangeSheet).Range(NameRangeCells)
If WorksheetFunction.CountBlankA(myRange) > 0 Then
    MsgBox "You must enter some data"
End If

A[L]C · 28 Sep 2006 at 12:35

doh

@ me

thanks

A[L]C · 28 Sep 2006 at 12:40

This appears to work... Any changes you reckon I should make??

Code:

Sub test3()

    RangeIsEmpty ("Printers")

End Sub

Function RangeIsEmpty(ByVal SourceRange As String) As Boolean

Set myRange = ActiveSheet.Range(SourceRange)
RangeIsEmpty = (WorksheetFunction.CountBlank(myRange) = myRange.Count)
If RangeIsEmpty = True Then
    MsgBox "enter some data"
End If
End Function

A[L]C · 28 Sep 2006 at 13:00

its going in to a validation sub which is triggered when they select the option from a command menu.

How would I create a variable which I could use that function to populate for multiple named ranges... ie

Named ranges:

Forename
Surname
Telephone_Number
Printers

Then if they have not filled in multiple sections they will get one error message at the end saying

Please fill in the following sections:

Telephone_Number
Printers.

Would this be a const or public variable or other?

A[L]C · 29 Sep 2006 at 08:07

|Thanks for your help. This all seems to work fine

Code:

'Require all variables to be defined
Option Explicit

'Declare public variables
Dim NotEntered As String
Dim ErrorCount As Integer
Public Validated As Boolean

Sub validate()
'Decalre variables
Dim n, result

'Reset variables in case this is not the first time it has been run
NotEntered = ""
ErrorCount = 0

'For each Named Range in the workbook, run the function to check if it is empty
For Each n In ActiveWorkbook.Names
    If Not Left(n.Name, 3) = "nr_" Then
        RangeIsEmpty (n.Name)
    End If
Next n

If ErrorCount = 0 Then
    result = MsgBox("All required fields populated.", vbOKOnly, "Form Validated Ok")
    Validated = True
ElseIf ErrorCount = 1 Then
    result = MsgBox("Please enter some data for the following field: " & vbCrLf & NotEntered, vbExclamation, "Missing Data")
Else
    result = MsgBox("Please enter some data for the following fields: " & vbCrLf & NotEntered, vbExclamation, "Missing Data")
End If

End Sub

Function RangeIsEmpty(ByVal SourceRange As String) As Boolean
'Declare variable
Dim myRange

'Set the variable to the range specified by the named range
Set myRange = ActiveSheet.Range(SourceRange)

'Count the number of blank cells in the range.  If True then there are no populated cells
RangeIsEmpty = (WorksheetFunction.CountBlank(myRange) = myRange.Count)

'If no populated cells, set public variable to the name of the named range
If RangeIsEmpty = True Then
    NotEntered = NotEntered & vbCrLf & SourceRange
    ErrorCount = ErrorCount + 1
End If
End Function