Validate MsgBox Entry to Data in Cells

G

Guest

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
 
D

Dave Peterson

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
 
G

Guest

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
 
D

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:

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???
 
G

Guest

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
 
G

Guest

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!!
 
G

Guest

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
 
G

Guest

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

Guest

Hi Dave,
I'm going userform crazy. Thanks again so much for your earlier help.
Had a weird that happen in a new one though. The two command buttons, 1 is
OK, 2 is Cancel, appear "switched" when the form is called, i.e. Cancel is
first and OK is 2nd. I'm hoping you can pick out the problem. Here is the
code I'm using:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex > 0 Then
Me.CommandButton2.Enabled = True
End If
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim password As String

Unload Me

Application.ScreenUpdating = False

With Worksheets("global setup")
Worksheets("Global Setup").Select
Range("CA3").Select
password = Range("CA3").Value
ActiveSheet.Unprotect (password)
With .Range("E3")
'.Value = CDate(Me.ComboBox1.Value)
.Value = (Me.ComboBox1.Value)
'.NumberFormat = "mmm dd, yyyy"
End With

Application.ScreenUpdating = True

Worksheets("Global Setup").Select
Worksheets("Global Setup").Rows("13").Hidden = True
Range("L5").Select
ActiveSheet.Protect (password)
End With

End Sub


Private Sub Label2_Click()

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("H2:H202")
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 ListRng.Cells
.AddItem myCell.Value
Next myCell
End With

End Sub


Thanks!
 
D

Dave Peterson

Part of the initialization code overwrites the captions.

If you've put the captions on manually, just remove that code (or put in what
you want).

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

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

But do make sure that the buttons do what you want.

Hi Dave,
I'm going userform crazy. Thanks again so much for your earlier help.
Had a weird that happen in a new one though. The two command buttons, 1 is
OK, 2 is Cancel, appear "switched" when the form is called, i.e. Cancel is
first and OK is 2nd. I'm hoping you can pick out the problem. Here is the
code I'm using:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex > 0 Then
Me.CommandButton2.Enabled = True
End If
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim password As String

Unload Me

Application.ScreenUpdating = False

With Worksheets("global setup")
Worksheets("Global Setup").Select
Range("CA3").Select
password = Range("CA3").Value
ActiveSheet.Unprotect (password)
With .Range("E3")
'.Value = CDate(Me.ComboBox1.Value)
.Value = (Me.ComboBox1.Value)
'.NumberFormat = "mmm dd, yyyy"
End With

Application.ScreenUpdating = True

Worksheets("Global Setup").Select
Worksheets("Global Setup").Rows("13").Hidden = True
Range("L5").Select
ActiveSheet.Protect (password)
End With

End Sub

Private Sub Label2_Click()

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("H2:H202")
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 ListRng.Cells
.AddItem myCell.Value
Next myCell
End With

End Sub

Thanks!
Click to expand...
 
G

Guest

Thanks much! That was simple. If you have time, I'm trying to overcome one
more challenge. I have a cell that a manager can enter Y to hide all payroll
data or N (the default) to display all payroll data. I have that cell
password protected with it's own seperate password, not the password for the
whole sheet. My boss likes the userforms so much, he wants me to create one
for the manager to change this cell. I've got the userform set up to allow Y
or N, but it's not calling the password for the cell before the change is
made. I don't want to give out the sheet password either.

If there were some way to have the userform reference another "hidden" sheet
and cell where a password just for this function was kept, and the user had
to enter that password in a textbox or by some other method as part of the
userform, this would work just as well and I could call the sheet password
and just keep the cell locked with the rest of the sheet. What solution would
you think best?


Here is the code in the sheet that controls the change in the cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$255" Then
If UCase(Target.Value) = "Y" Then
Call HideAllWageData
MsgBox "The Wages For All Plumbers Has Been Hidden."
Else
If UCase(Target.Value) = "N" Then
Call ShowAllWageData
MsgBox "The Wages For All Plumbers Are Now Visible."
Else
Me.Unprotect password:=Range("CA3").Value
Me.Protect password:=Range("CA3").Value
MsgBox "Enter A Valid Response Y or N."
End If
End If
End If
End Sub

Here is the code for the userform:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex > 0 Then
Me.CommandButton1.Enabled = True
End If
End Sub
Private Sub CommandButton1_Click()
Dim password As String

Unload Me

Application.ScreenUpdating = False

With Worksheets("global setup")
Worksheets("Global Setup").Select
Range("CA3").Select
password = Range("CA3").Value
ActiveSheet.Unprotect (password)
With .Range("D255")
'.Value = CDate(Me.ComboBox1.Value)
.Value = (Me.ComboBox1.Value)
'.NumberFormat = "mmm dd, yyyy"
End With

Application.ScreenUpdating = True

Worksheets("Global Setup").Select
Worksheets("Global Setup").Rows("13").Hidden = True
Range("L5").Select
ActiveSheet.Protect (password)
End With
End Sub
Private Sub CommandButton2_Click()

Unload Me

End Sub
Private Sub Label2_Click()

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("A2:A4")
End With

With Me.CommandButton1
.Enabled = False
.Caption = "OK"
End With

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

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

End Sub
 
D

Dave Peterson

I'm not sure how all this works together, but how about just showing another
userform when they want to hide/show the wage data.

You can pick up the password from a hidden sheet or just bury it in your code.

I created a userform with a textbox, a checkbox (equivalent to y/n), a label
(for messages) and two buttons.

This was the code behind that userform:

Option Explicit
Private Sub CommandButton1_Click()
If Me.CheckBox1.Value = True Then
Call HideAllWageData
Me.Label1.Caption = "The Wages For All Plumbers Has Been Hidden."
Else
Call ShowAllWageData
Me.Label1.Caption = "The Wages For All Plumbers Are Now Visible."
End If
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub TextBox1_Change()
Dim myPwd As String

'myPwd = "hithere"
'or
myPwd = Worksheets("hiddensheetnamehere").Range("d255").Value

If Me.TextBox1.Value = myPwd Then
Me.CommandButton1.Enabled = True
Me.CheckBox1.Enabled = True
Else
Me.CommandButton1.Enabled = False
Me.CheckBox1.Enabled = False
End If
End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.Caption = "Ok"
.Enabled = False
End With
With Me.CommandButton2
.Caption = "Cancel"
.Enabled = True
End With
With Me.CheckBox1
.Value = False
.Caption = "Hide wages for all Plumbers"
.Enabled = False
End With
Me.Label1.Caption = ""
Me.TextBox1.PasswordChar = "*"
End Sub

Maybe you can make it fit in????
 
G

Guest

Thank you again so much!! I'm using this approach, and have taken the
password out of the individual cell, locking it with the rest of the sheet,
and have the real password buried in a hidden sheet. Thanks much again!

David
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need to Improve Code Copying/Pasting Between Workbooks 1
Need to loop my macro 3
Put Filename Variable in Macro 4
iValue Stops after 10 cells of data 1
RT Error 438 7
Runtime Error 1004 when runing Sub() from btnClick 2
Find Cell in Named Range 3
Put Filename Variable in Macro 1

Top