Data Validation Possibly?

colmcg

Board Regular
Joined
Jul 2, 2004
Messages
101
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?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
colmcg said:
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

Board Regular
Joined
Jul 2, 2004
Messages
101
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
colmcg said:
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

Board Regular
Joined
Jul 2, 2004
Messages
101

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
If you want to use COUNTBLANK, or any other worksheet function you need to use the WorksheetFunction method.

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

colmcg

Board Regular
Joined
Jul 2, 2004
Messages
101

ADVERTISEMENT

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-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 2, 2004
Messages
101
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,457
Members
412,595
Latest member
slim313
Top