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?

WIBSBOT · 27 Sep 2006 at 16:13

you don't necessarily need any vba code to do this - you can do it all by naming a range and chosing data validation although this might be too simplistic for you.

if not give the below a go (you probably need to change it a lot)

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myRange As Range
    Set myRange = Worksheets("Sheet1").Range("A1:D5")
    myRange.Font.Bold = True
    
    If RangeIsEmpty(myRange) = False Then
        MsgBox "enter some data"
    End If

End Sub

Function RangeIsEmpty(ByVal SourceRange As Range) As Boolean

  RangeIsEmpty = (WorksheetFunction.CountBlank(SourceRange) = SourceRange.Count)
  
End Function

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?

WIBSBOT · 28 Sep 2006 at 10:50

A[L]C said:
How do I get vba to look at each cell in that range, and return an error if they are all blank?

This works if any of the cells in the range are blank and gives you the message everytime you move in the worksheet. Might need some adapting so that the message doesn't get irritating.....

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myRange As Range
    Set myRange = Worksheets("Sheet1").Range("A1:B2")
    myRange.Font.Bold = True
    
    If WorksheetFunction.CountBlank(myRange) > 0 Then
        MsgBox "You must enter some data"
    End If
End Sub

the code below works if all the cells in the range are blank.....

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myRange As Range
    Set myRange = Worksheets("Sheet1").Range("A1:B2")
    myRange.Font.Bold = True
    
    If RangeIsEmpty(myRange) = True Then
        MsgBox "enter some data"
    End If

End Sub

Function RangeIsEmpty(ByVal SourceRange As Range) As Boolean

  RangeIsEmpty = (WorksheetFunction.CountBlank(SourceRange) = SourceRange.Count)
  
End Function

hope this helps

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)

WIBSBOT · 28 Sep 2006 at 11:57

A[L]C said:
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?

I don't think I quite understand what you are trying to do but why don't you refer to the range in the code rather than trying to strip out the address of the range ?
i.e.

I define the range on my spreadsheet manually - call it test1 and then change my code so it refers to Test1....

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim myRange As Range
    'Set myRange = Worksheets("Sheet1").Range("A1:B2")
    Set myRange = Worksheets("Sheet1").Range("test1")
    

    myRange.Font.Bold = True
    
    If WorksheetFunction.CountBlank(myRange) > 0 Then
        MsgBox "You must enter some data"
    End If
End Sub

There are lots of ways of getting the address without having to play around with strings.....

Set myRange = Worksheets("Sheet1").Range("$A$1:$B$2") <-----works ok.

Set myRange = Range("Sheet1!$A$1:$B$2") <----- works ok.

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

WIBSBOT · 28 Sep 2006 at 12:31

Set myRange = Range("printers") will work ??????

and then use one of the two code examples for searching the all or any of the range. You don't need any of that string editing do you ?

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

WIBSBOT · 28 Sep 2006 at 12:55

A[L]C said:
doh

@ me

thanks

I've done things like that in the past - and I'm sure I'll continue to do them.

A[L]C said:
This appears to work... Any changes you reckon I should make??

looks good although it could be trimmed down a bit - you've just got to decide where to put it otherwise the user will get a message everytime they enter a cell.

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?

WIBSBOT · 28 Sep 2006 at 13:08

A[L]C said:
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?

I don't know if this will work but you can refer to multiple ranges at a time by doing the following.....

Range("Forename, Surname, Telephone Number").

other than that it might just be writing some code ! and setting an error message variable.... i.e.

Sub test3()

msgstring=""

If RangeIsEmpty("forename") = True Then
msgstring = "forename"
End If

If RangeIsEmpty("surname") = True Then
msgstring = msgstring + "surname"
End If

if msgstring <> "" then
msgbox "Please enter the following " & msgstring
end if

End Sub

Function RangeIsEmpty(ByVal SourceRange As String) As Boolean

Set myRange = ActiveSheet.Range(SourceRange)
RangeIsEmpty = (WorksheetFunction.CountBlank(myRange) = myRange.Count)
End Function

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

WIBSBOT · 29 Sep 2006 at 10:31

looks good - glad to be of help