Data Validation Possibly?

[colmcg]

In column B (B2:B21) I want users to enter only WHOLE numbers between 1 & 20 (no specific order). Once the user has entered all 20 numbers I have recorded a macro to Sort my table by column B, ascending.

Using Data Validation I can restrict the user to entering only a whole number between 1 & 20 but can I also use data validation to stop any 'duplicate' entries. i.e. I only want the user to be able to enter each number once.

Can this be done using Data Validation?

 

[Aladin Akyurek]

In column B (B2:B21) I want users to enter only WHOLE numbers between 1 & 20 (no specific order). Once the user has entered all 20 numbers I have recorded a macro to Sort my table by column B, ascending.

Using Data Validation I can restrict the user to entering only a whole number between 1 & 20 but can I also use data validation to stop any 'duplicate' entries. i.e. I only want the user to be able to enter each number once.

Can this be done using Data Validation?

If you choose Custom for Allow and invoke:

=($B2>0)*($B2<=20)*($B2=INT($B2))*(COUNTIF($B$2:$B$21,$B2)<=1)

you would get what you want.

 

[colmcg]

Aladin,

Thanks very much, works like a dream.

One more question if I may. I need to ensure that the user has entered all 20 numbers prior to running the 'Sort' macro. What piece of code do I need to add to check that there are no blank cells in the range B2:B21. If there are any blanks a message box will appear to inform the user there are blank cell/s.

In other words I don't want the sort macro to run until all 20 numbers have been input.

Thanks in advance.

 

[Aladin Akyurek]

Aladin,

Thanks very much, works like a dream.

One more question if I may. I need to ensure that the user has entered all 20 numbers prior to running the 'Sort' macro. What piece of code do I need to add to check that there are no blank cells in the range B2:B21. If there are any blanks a message box will appear to inform the user there are blank cell/s.

In other words I don't want the sort macro to run until all 20 numbers have been input.

Thanks in advance.

You could include a check with COUNTBLANK(Range) in your macro, but I have no idea how that's done in VBA.

 

[colmcg]

Thanks for your time and assistance Aladin.

I wouldn't know how to use COUNTBLANK in a macro either. My VBA is very limited however, thanks to this site, it is improving. I've done a search but cannot find a post similar to what I am trying to achieve.

Can anyone point me in the right direction?

 

[Norie]

If you want to use COUNTBLANK, or any other worksheet function you need to use the WorksheetFunction method.

If Application.WorksheetFunction.CountBlank(Range("B2:B21"))<>0 Then
     MsgBox "There are blanks in the range B2:B21."
End If

 

[colmcg]

Norrie,

Thank you for your input. I added your code at the start of my macro and a message box appears if there are any blanks in the range. However when I click the message box the macro then procedes to sort the 'incomplete' data.

I only want the macro to perform the sort if all 20 numbers have been entered. How do I stop the macro if it finds blanks in B2:B21.

 

[Norie]

Well what I posted was just an example.

I don't really know how you would incorporate it in your code, because, well, I haven't seen your code.

 

[colmcg]

Norrie,

This is what I have so far.

Sub Sort()
'
' Sort Macro
' Macro recorded 27/11/2005 by
'

'
If Application.WorksheetFunction.CountBlank(Range("B2:B21")) <> 0 Then
MsgBox "There are blanks in the range B2:B21."
End If
Range("B1:E21").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B2:B21").Select
Selection.ClearContents
Range("A1").Select
End Sub


What I would like to happen is, if there are any 'blanks' in B2:B21 the message box would inform the user there are blanks and then the macro would then stop, i.e. it would not perform the sort.

 

[Norie]

Try this.

Sub Sort() 

If Application.WorksheetFunction.CountBlank(Range("B2:B21")) <> 0 Then 
     MsgBox "There are blanks in the range B2:B21." 
     Exit Sub
End If 

Range("B1:E21").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
Range("B2:B21").ClearContents 
End Sub