I'm confused about how the pre-entered data is used to validate the entry.

I see that you want the user to match a date in sheets(2) (not sure the name of
it) in row 1. I don't see how G2:G53 (On Index) is used at all.

If all you're doing is forcing the user to choose a date from that list, maybe
designing a useform with a combobox on it that only allows those dates to be
chosen would be better.

And (maybe) adding a combobox that shows the entries in that Index!G2:G53 would
be a way to allow the user to select from those entries.

If that sounds ok, this might get you closer. (I didn't include any
unprotecting and the sheetnames may be wrong--but other than that....)

I created a userform with two commandbuttons and two comboboxes.

This was the code that was behind the form:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex 0 _
And Me.ComboBox2.ListIndex 0 Then
Me.CommandButton2.Enabled = True
End If
End Sub
Private Sub ComboBox2_Change()
If Me.ComboBox1.ListIndex 0 _
And Me.ComboBox2.ListIndex 0 Then
Me.CommandButton2.Enabled = True
End If
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
With Worksheets("global setup")
With .Range("e5")
.Value = CDate(Me.ComboBox1.Value)
.NumberFormat = "mmm dd, yyyy"
End With
With .Range("f5")
.Value = Me.ComboBox2.Value
End With
End With

Unload Me

End Sub
Private Sub UserForm_Initialize()

Dim myCell As Range
Dim DateRng As Range
Dim ListRng As Range

With Worksheets("sheet1")
Set DateRng = .Range("a1", .Cells(1, .Columns.Count).End(xlToLeft))
End With

With Worksheets("index")
Set ListRng = .Range("G2:G53")
End With

With Me.CommandButton1
.Enabled = True
.Caption = "Cancel"
End With

With Me.CommandButton2
.Enabled = False
.Caption = "Ok"
End With

With Me.ComboBox1
.Clear
.Style = fmStyleDropDownList
For Each myCell In DateRng.Cells
.AddItem Format(myCell.Value, "mmm dd, yyyy")
Next myCell
End With

With Me.ComboBox2
.Clear
.Style = fmStyleDropDownList
For Each myCell In ListRng.Cells
.AddItem myCell.Value
Next myCell
End With

End Sub

If you've never used a userform, Debra Dalgleish has some notes at:
http://contextures.com/xlUserForm01.html



David wrote:

I want to prompt the user to enter a date from a message box, have the entry
be validated with pre-entered data in cells, and once verified, write the
date to one cell.
The pre-entered data is on worksheet "Index" in column G, rows 2-53. The
data are all the Sunday dates in 2006 in mmm-dd-yy format. I want to write
the result to worksheet "Global Setup" cell E5. The index sheet is normally
hidden and password protected.
Here is what I have so far:

Dim wCtr As Long
Dim iOffice As Integer, iDate As Date, iValue
Dim password As String

Application.ScreenUpdating = False

Worksheets("Global Setup").Select
Range("CA3").Select
password = Range("CA3").Value

ActiveSheet.Unprotect (password)

Worksheets("index").Visible = xlSheetVisible

Worksheets("Global Setup").Select
Range("E5").Select

iDate = CLng(Application.InputBox(prompt:="Enter the Date of the Next
SUNDAY. (mm/dd/yy) Are you Ready?", Type:=1))

Msg = "Enter the Date of the Next SUNDAY. (mm/dd/yy) Are you Ready?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then Exit Sub

' check iDate - Variance to matched row and column
With wbSum.Sheets(2)
Dim lastrow As Long, lastcol As Long, xV As Long, xR As Long, xC As Long
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column

' get matching column
For xV = 1 To lastcol
If iDate = .Cells(1, xV) Then xC = xV
Next xV
If xC = 0 Then MsgBox "Date: " & iDate & " not found in Date table"

End With

Worksheets("Global Setup").Select
Range("E5").Select = iDate

ActiveSheet.Protect (password)
'
End Sub

--

Dave Peterson