Remember that the code goes behind the userform itself--not in a general module,
not behind a worksheet and not behind ThisWorkbook.

Looking at your original code, it looked like the dates were in a single row:

For xV = 1 To lastcol
If iDate = .Cells(1, xV) Then xC = xV
Next xV

But I was confused about what you wanted.

If you have trouble modifying the code, post back what you've done with an
explanation of what you want.

Maybe it'll be as simple as getting rid of combobox2 and changing the range for
the dates???




David wrote:

Hi Dave, Thanks so much for steering me in the right direction. I've never
used user forms, so I have a couple questions.
First, sheet1 is named Index and the valid dates are on sheet1(Index) in
cells G2:G53.
I agree that having the user pick one of the predefined dates is the ideal
solution!! It saves a lot of work and narrows the potential for errors.
I copied your code into VBA and ran it from there. I go an error when it got
to the Me. statements.
I'm probably not setting it up right, so could you tell me how to make it
work?
I tried creating a user form and putting your code there, but could not get
it to work either. I would be grateful for any additional information.
Thanks much!

David

"Dave Peterson" wrote:

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


--

Dave Peterson