Solved: Data Validation Macro Problem

[JGalper]

In my worksheet for the column C, I have the data validation set to =ISNA(MATCH($C1,OffDays,FALSE)), where OffDays is a list on another sheet that does not allow users to input those dates.

I want to create a macro that will search through column C when clicked and on error, display an input box that will allow users to input a date so the cell no longer contains the data validation error.

I would also like the box to come up when someone inputs a value that triggers the data validation error, so they can type in a new date.

Any ideas?

[Apps]

Hi,

Surely if you do this;

I would also like the box to come up when someone inputs a value that triggers the data validation error, so they can type in a new date.

;using Data>Validation options in the Cells they key into,
then you wouldn't necessarily need this;

I want to create a macro that will search through column C when clicked and on error, display an input box that will allow users to input a date so the cell no longer contains the data validation error.

;as the cells would be self validating?

[p45cal]

First, the attached is very basic; I leave the addition of bells and whistles to you (such as labels on the userform telling users what's required).
For others not wishing to download/open the attachment, the solution comprises a userform called userfom1 with a Date &Time Picker called DTPicker1 and a command button called CommandButton1 with the caption OK.
Then there's an activex command button on the worksheet called CommandButton1. The code in the worksheet module is:
[vba]Private Sub CommandButton1_Click()
CheckColumnC
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Set myRange = Intersect(Columns(3), Target)
If Not myRange Is Nothing Then CheckARange myRange
End Sub
[/vba] and code in the userform module:
[vba]Private Sub CommandButton1_Click()
UserForm1.Hide
cll.Value = Me.DTPicker1.Value
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.DTPicker1.Value = cll.Value
End Sub
[/vba] and the entire code of a standard code module:
[vba]Public cll As Range 'this line must be in the Declarations part (the top) of the module.

Sub CheckColumnC()
Set myRange = Intersect(ActiveSheet.UsedRange, Columns(3))
CheckARange myRange
End Sub

Sub CheckARange(theRange)
For Each cll In theRange.Cells
Set xxx = Range("Offdays").Find(cll.Value)
If Not xxx Is Nothing Then
cll.Select
UserForm1.Show
End If
Next cll
End Sub
[/vba]Since it is its own data validation, you can lose the cell Data Validation because it becomes messy with two mechanisms checking data.
Column E in the attachment is not needed, lose it, it was just there to show me whether dates were valid or not.
It's rough and ready but should give you the Any ideas? you wanted.

[JGalper]

I've edited everything around to names that work for my Workbook, and changed the command from a button to an add-in. It appears to come up when I type in a date that is in the list (intended outcome), but the first time I type in a date, I get an error that says "User type not defined". If I click the Add-In button, it starts selecting dates that are not on the list as errors. I'll attach my code below.

In the worksheet (Named Sheet1(Production Schedule)):[vba]
Private Sub ShowDateCheckerForm_Click()
CheckColumnC
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Set myRange = Intersect(Columns(3), Target)
If Not myRange Is Nothing Then CheckARange myRange
End Sub
[/vba]

In the workbook (I deleted the other toolbar references, but they are all referenced like this):[vba]Option Explicit
Dim tbarpos As ToolbarPosition
Private Sub Workbook_Deactivate()
Call RemoveToolbar("Production Schedule")
End Sub
Private Sub Workbook_Activate()
Const tBarName As String = "Production Schedule"
Dim cControl As CommandBarButton
' Re-adds the toolbar
If (ToolBarExists(tBarName) = False) Then Call CreateToolbar(tBarName, tbarpos)

' Add all the buttons
Call CreateToolBarButton(tBarName, "Check for Invalid Dates", "ufDateChecker.Show")
End Sub
[/vba]

In the userform:[vba]Private Sub CommandButton1_Click()
ufDateChecker.Hide
cll.Value = Me.DTPicker1.Value
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End
End Sub

Private Sub UserForm_Initialize()
Me.DTPicker1.Value = cll.Value
End Sub
[/vba]

In the module that I named DateCheckerForm (Had to name it this because I couldn't track down an error that occured when I named it something else):[vba]Public cll As Range
Sub ShowDateCheckerForm()
Set myRange = Intersect(ActiveSheet.UsedRange, Columns(3))
CheckARange myRange
End Sub
Sub CheckARange(theRange)
For Each cll In theRange.Cells
Set xxx = Range("Offdays").Find(cll.Value)
If Not xxx Is Nothing Then
cll.Select
ufDateChecker.Show
End If
Next cll
End Sub
[/vba]

In a module called ToolbarFunctions:[vba]
Function ToolBarButtonExists(ByVal tBarName As String, ByVal tButtonName As String) As Boolean
' Determines if a toolbar button exists in the application
On Error Resume Next
Dim tButton As CommandBarButton
If ToolBarExists(tBarName) = False Then ToolBarButtonExists = False: Exit Function
Set tButton = Application.CommandBars(tBarName).Controls(tButtonName)
ToolBarButtonExists = Not tButton Is Nothing
End Function
Sub CreateToolBarButton(ByVal tBarName As String, ByVal tButtonName As String, _
ByVal strOnAction As String, Optional blnSeparator As Boolean = False)
' Creates toolbar buttons to a given toolbar
Dim tBar As CommandBar
Dim tButton As CommandBarButton

If ToolBarExists(tBarName) = False Then Exit Sub
If (ToolBarButtonExists(tBarName, tButtonName) = True) Then Exit Sub

Set tBar = Application.CommandBars(tBarName)
Set tButton = Application.CommandBars(tBar.Name).Controls.Add(Type:=msoControlButton)

With tButton
.Caption = tButtonName
.OnAction = strOnAction
.Style = msoButtonCaption
.Width = 400
If blnSeparator = True Then .BeginGroup = True
End With
End Sub

[/vba]

First, the attached is very basic; I leave the addition of bells and whistles to you (such as labels on the userform telling users what's required).
For others not wishing to download/open the attachment, the solution comprises a userform called userfom1 with a Date &Time Picker called DTPicker1 and a command button called CommandButton1 with the caption OK.
Then there's an activex command button on the worksheet called CommandButton1. The code in the worksheet module is:
[vba]Private Sub CommandButton1_Click()
CheckColumnC
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Set myRange = Intersect(Columns(3), Target)
If Not myRange Is Nothing Then CheckARange myRange
End Sub
[/vba] and code in the userform module:
[vba]Private Sub CommandButton1_Click()
UserForm1.Hide
cll.Value = Me.DTPicker1.Value
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.DTPicker1.Value = cll.Value
End Sub
[/vba] and the entire code of a standard code module:
[vba]Public cll As Range 'this line must be in the Declarations part (the top) of the module.

Sub CheckColumnC()
Set myRange = Intersect(ActiveSheet.UsedRange, Columns(3))
CheckARange myRange
End Sub

Sub CheckARange(theRange)
For Each cll In theRange.Cells
Set xxx = Range("Offdays").Find(cll.Value)
If Not xxx Is Nothing Then
cll.Select
UserForm1.Show
End If
Next cll
End Sub
[/vba]Since it is its own data validation, you can lose the cell Data Validation because it becomes messy with two mechanisms checking data.
Column E in the attachment is not needed, lose it, it was just there to show me whether dates were valid or not.
It's rough and ready but should give you the Any ideas? you wanted.

[p45cal]

Perhaps:
Call CreateToolBarButton(tBarName, "Check for Invalid Dates", "ufDateChecker.Show")
should be:
Call CreateToolBarButton(tBarName, "Check for Invalid Dates", "ShowDateCheckerForm")
(in haste).

re:"the first time I type in a date, I get an error that says "User type not defined"
What line of code is highlighted when you choose to debug?
It could be a variable not being defined in a module with Option Explicit on.

[JGalper]

I also forgot to add, in another module where the other definitions are called out, I have:[vba]Option Explicit
Option Private Module
Public Sub ShowDateCheckerForm()
ufDateChecker.Show
End Sub[/vba]

And I changed to what you said above, but I am still getting the error. I am going to copy everything over to a new sheet to see if I can replicate it, and if so, I will attach it here.

Edit: Original code was incorrectly copied

[JGalper]

re:"the first time I type in a date, I get an error that says "User type not defined"
What line of code is highlighted when you choose to debug?
It could be a variable not being defined in a module with Option Explicit on.

My code was not saving the code I posted right above calling the Toolbar button.

When I name it as sugguested, I get an error when I click the button:
Cannot run the macro "Test Excel.xlsm'!ShowDateCheckerForm'. The macro may not be available in this workbook or all macros may be disabled.

[p45cal]

So you have two subs called ShowDateCheckerForm, one in a Private module. I don't know off the top of my head what this means in terms of scoping but surely they don't need to have the same name.

Yes, create a new workbook which duplicates the problems and attach here.

[JGalper]

Here is the extracted file, let me know what you come across.

[p45cal]

I'm having a bit of difficulty; I sent you an xls file developed under xl2003, you sent me an xlsm file developed under xl20??. (Which version?)
I tried to open it in xl2010 but immediately had 2 problems:
1. It didn' like our DTPicker (solved that bit now by installing Common Controls 2)
2. It didnt recognise the type here:
Dim tbarpos As ToolbarPosition

So could you either send me an xls version and/or fill me in with what I may need to do to have ToolBarPosition recognised (references needed in the VBE or code/class module containing said Type, or anything else).

[JGalper]

Here is the attached file. My friend gave me the ToolbarPosition code. I believe it is used to format the toolbar location and order of add-ins.

[p45cal]

You're still hiding the type definition for ToolbarPosition from me!
Try deleting (or at very least commenting-out) the entire Public Sub ShowDateCheckerForm() sub in the MyFunctions code module - as I said in post #8, you shouldn't have two subs with the same name.

[JGalper]

p45cal -
What I ended up doing was reverting back to your original code and starting over to see if I can work around the problems. I found an alternate way to create a toolbar (I don't want to deal with tbarpos!!!) to add this as a toolbar button instead of the activex control.

Regardless of that, the problem appears to start when running the macro. When I type in a date it knows that it is in the list and pops up with the form, but when I manually start the macro, it appears to start checking the cells and giving me the error if they are NOT in the list. The only thing I can think of is that I tell the toolbar button to run the macro "CheckColumnC" when clicked, which may cause it to try and match the cells with the list, instead the opposite, which is the intended function.

Edit: Updated error function

[p45cal]

Somewhere in the code you should have:
Type ToolbarPosition
search for it in the code - I have found some code on the interweb, does it read as follows?:
[vba]Type ToolbarPosition
Top As Long
Left As Long
Position As Variant
RowIndex As Variant
End Type[/vba] Hopefully yes.
Attached is something which hopefully will work, activex objects permitting.

[JGalper]

Somewhere in the code you should have:
Type ToolbarPosition
search for it in the code - I have found some code on the interweb, does it read as follows?:
[vba]Type ToolbarPosition
Top As Long
Left As Long
Position As Variant
RowIndex As Variant
End Type[/vba] Hopefully yes.
Attached is something which hopefully will work, activex objects permitting.

In another module in the workbook, under the name "TypeVariables" I found it:
[VBA]Option Explicit

' For use of remembering where the custom toolbar was placed
Public Type ToolbarPosition
Top As Long
Left As Long
Position As Variant
RowIndex As Variant
End Type
[/VBA]

When you run the macro on the attachment you just sent, what is the first date it pulls to correct? For me it pulls 1/5/11, which is not in the list.

[p45cal]

When you run the macro on the attachment you just sent, what is the first date it pulls to correct? For me it pulls 1/5/11, which is not in the list.

That took me by surprise!
change to the following in red:
[vba]Sub CheckARange(theRange)
For Each cll In theRange.Cells
Set xxx = Range("Offdays").Find(cll.Value, LookIn:=xlFormulas, lookat:=xlWhole)
If Not xxx Is Nothing Then
cll.Select
ufDateChecker.Show
End If
Next cll
End Sub
[/vba]and check again.

One more thing, I notice thre's a filter at the top of the Offdays range, were you hoping to use this to exclude some dates in the list from being invalid? At the moment, it will 'see' all the dates, regardless of you filter the list and you can't see some dates.

[JGalper]

p45cal, I changed the definition for the macro in the MyFunctions module:

[VBA]Public Sub ShowDateCheckerForm()
CheckColumnC
End Sub[/VBA]

This seems to make everything work properly.

I'm going to be running it through testing, but so far, everything seems to be functioning excellently!

Thank you so much, you've been of unbelievable assistance!

[p45cal]

I don't have the code for CheckColumnC so I can't advise!
Did you also make the changes suggested in post #16?

[JGalper]

I don't have the code for CheckColumnC so I can't advise!
Did you also make the changes suggested in post #16?

The code I am referring to was for referencing what ShowDateCheckerForm() called when clicked. There is no problem, I was just informing you.

Also, the changes in post #16 are what helped fix the issue.