UserForm

[SeanJ]

Greeting everyone,

I have modify a userform from http://www.contextures.com/xlUserForm02.html and added a date picker. I the following questions:

How can I change the date shown on the date picker to be 13-May-08? :banghead:
This spreadsheet will be sent to people I need to password protect the VBA code, and all sheets? :dunno
Is there a way stop people from adding spreadsheets? :dunnoI will continue search the Internet for answers to questions 2 & 3.

[Dr.K]

1. Not sure what you are asking. If you just need to set the value, use the value property?

comboDate.value = "13-May-08"

Remember, combo/drop box data is stored as Variant, so wether or not Excel recognizes the dates as dates is determined by your Excel settings.

2. NO, do not protect the code. It causes more trouble then its worth, and it is easily defeated by those who know what they are doing. Its ok to use passwords for Worksheet and Workbook level Protection, though.

3. Yes, Protecting the Workbook prevents them from adding/modifing the worksheets. Protecting the individual sheets prevents them from modifing the Cells or Worksheet Objects

[Bob Phillips]

1. dtDatepicker.Value = Date

2. In the VBIDE, select the project, and goto Tools>Project Properties>Protection and set it there. It is very weak protection though.

3. A particularly brutal way



Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[SeanJ]

1. dtDatepicker.Value = Date

2. In the VBIDE, select the project, and goto Tools>Project Properties>Protection and set it there. It is very weak protection though.

3. A particulalrly brutal way



For #1 where do I put it? :think:

For #2 The personnel getting this spreadsheet are not VBA or Excel wise. :rofl:

For #3 Works like a charm!! :clap:

edit: date picker not able to display today's date as 14-May-08. Help

[Bob Phillips]

For #1 where do I put it? :think:

Only you know that. It is a design decision.

[SeanJ]

Only you know that. It is a design decision.

DOH! :doh:

One quick question:

How do I protect the worksheet in VBA so that only the information from the userform is added and nothing else?

[SeanJ]

I got the answer to #1 pate picker. I had to go into the property of the date picker and clicked (custom) and I got the "..." to the right of it. In the CustomFormat box I typed "d-MMM-yy".

[Norie]

Sean

All that will do is format the datepicker - it's not going to set it's value.

And there's already code that appears to be trying to do that in the initialize event.

Me.dtDatepicker.Value = Format(Date, "Medium Date")

But I would suggest you lose the Format... and just use Date.

[SeanJ]

Sean

All that will do is format the datepicker - it's not going to set it's value.

And there's already code that appears to be trying to do that in the initialize event.

Me.dtDatepicker.Value = Format(Date, "Medium Date")

But I would suggest you lose the Format... and just use Date.

Norie,

I am formatting the date in the VBA to "Medium Date". Also I figured out how to protect and unprotect the sheets.

Now for a little advice. After a user enters data to the worksheet and then realize they made a mistake I need to call that row in a userform for the user to correct and then repost it back to it orginal spot. :bug:

[SeanJ]

Sorry here is the updated workbook pw=1234.

Don't worry the final pw will be different. :rotlaugh:

[SeanJ]

Here is what I got so far. Not much

[SeanJ]

Ok here is what I got so far with adding, finding and editing data. I know I have a weak find method I would like help with that area if possible. Also how do I keep people from unhiding or deleting a protected sheet.

Thanks