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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

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,218
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,218
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,218
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,122,585
Messages
5,597,029
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top