Date Validation via Combobox in VBA

[naeem.mahmood]

Hi,

I have created a user form to update the balanaces in the accounts.
User can pick the dates, which are user can choose by a combox box and
update accordingly.

I want to have a validation check for the date so that the user are
not allowed to pick dates which have balances already attached to
them.

I tried creating the following but its not working. I am new to VBA so
if someone can help me move in the right direction that would be
really helpful.

---------------------------------------------------

Private Sub cmblist_Change()

DateCheck

End Sub

----------------------------------------------------

Private Sub DateCheck()


If cmblist.Value < Sheets("CDN").Range("DateDBCDN") Then

' DateDBCDN is the range where the dates are posted from the user
form.

msg = "Data for this date is already posted"
Sheets("Main").Select
Range("A2").Select
dialogstyle = vbOKOnly + vbCritical
Title = "Invalid Date"
Response = MsgBox(msg, dialogstyle, Title)

End If
End Sub

-----------------------------------------------------------------------

 

[merjet]

I'm confused. A ComboBox is for offering the user a prescribed set of
choices. A list of specific dates fits that. Yet you seem to want the
user to choose a date which is NOT in a prescribed set.

Also, your line:
If cmblist.Value < Sheets("CDN").Range("DateDBCDN") Then
can't work when the left side is a one value and the right side is
presumably several values. There are some computer languages with
which one can compare a scalar to a vector and get a vector answer.
Indeed, some Excel worksheet formulas allow that. However, VBA isn't
one of them.

Hth,
Merjet

 

[naeem.mahmood]

Thanks for the reply.

What I am trying is to give user the combo box to pick up a date to
input the data.
Date is in a combo box and then data goes in separate boxes.

I dont want them to pick a date which has already data associated with
it or in otherwords the account balances for that date has already
been updated. Since its a combobox all the dates will show (my
understanding) but I want to put a warning message so if they pick a
already input date from combobox it should a warning message should
come up.

I am a beginner in VBA so I may be on the wrong track.

The code using cmblist.value, I m trying to check the combobox value
with a range in worksheet (A4:A365).

I hope I made my self clear this time.

Thanks for the help.

NM

 

[merjet]

It sounds like an InputBox, TextBox, Calendar control, or Monthview
control would be a better means. The first doesn't even need a
UserForm. If the latter two are not on your Toolbox (where you got the
ComboBox), right-click on the Toolbox, then click Additional Controls,
then check Calendar Control or Microsoft Monthview control.

Hth,
Merjet

 

[JLGWhiz]

If you are loading your combo box from a row source, you might consider
writing code to delete the used date from the row source as it is used. That
would leave only valid dates to select from. Without knowing how you load
the control, that is the best I can offer.

 

[naeem.mahmood]

Yes I am loading the combobox from a row source.

I think that would be easier but then again if I delete the rows that
have been used, wouldnt it show a blank dropdown menu untill that date
is shown.

Naeem