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

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

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

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

I went into VBA and created the userform. I then added the two command
buttons, and the two comboboxes and one list box. I then double clicked on
the form and pasted your code into it and then ran it. I'm getting errors.
I think I was trying to make it too hard and I REALLY appreciate all your
help.
All I'm looking to do is have the user pick a valid date and write that date
into cell E5 on the "Global Setup" page.
The valid dates are on a sheet named "index" (which is sheet1) and are
currently in cells G2:G53 (one column, not row). I can just as easily put
them in column A.
I just want a button to start the macro, bring up the userform with the
available dates to select, have the user select the date and then have the
selection written to cell E5. I'm sorry I was way too complex.
And any steps I'm missing in setting up the initial userform, would be
helpful. Thank so much again!

David
"Dave Peterson" wrote:

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

OK...I have the code working!!
I deleted combobox1 and am just using #2. It's writing to the correct cell
as well.
I just need to know how to start the userform from the worksheet.
Thanks so much!!

"Dave Peterson" wrote:

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

This is the code that I ended up with that is working perfectly. Thanks again!

Option Explicit
Private Sub ComboBox2_Change()
If 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.ComboBox2.Value)
.NumberFormat = "mmm dd, yyyy"
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("index")
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

"Dave Peterson" wrote:

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

Got it all! Thanks again!! Created a button and asigned the userform with
..show command. Thanks SO MUCH AGAIN!!

"Dave Peterson" wrote:

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

Glad you got it working.

David wrote:

Got it all! Thanks again!! Created a button and asigned the userform with
.show command. Thanks SO MUCH AGAIN!!

"Dave Peterson" wrote:

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


--

Dave Peterson