Disable frames & option buttons on userform based on today's date

**raj.bris** · 03-01-2013, 10:01 PM

Hi Experts

I am new to VBA and wondering if this is possible to disable or lock the option buttons and the frames if today's date is greater than the date in a particular cell of a worksheet. I have a worksheet called "Raj" and in there a range A1 to A75 contains various dates in increasing order. Some dates repeat as well. e.g.
ColoumnA ColoumnB

A1 3-April-2013
A2 4-April-2013
A3 4-April-2013
A4 5-April-2013
A5....
.
.
A75 26-May-2013

In a userform1 I have around 75 frames and each frame has two option buttons. User is allowed to select or make any change in Ist frame only if today's date is less than 3rd of April 2013 otherwise the frame should be locked or disabled. Same will apply to the following frames, may be for loop or something like that is required.

Also, is it possible to have the pre selected values restored on the userform when it is called again?

I have done some searches around and have not found any solution to this so far. I would really appreciate your help in this.

Regards
Raj

**davesexcel** · 03-01-2013, 10:30 PM

Raj ,
You have not given anything for somebody to work with. How can anybody give you a decent answer?

**evanzo** · 03-01-2013, 10:40 PM

If I read this correctly, the first part is that you want to disable some userform controls (buttons) based on today's date meeting a criteria. The second part, you want the userform to have pre-loaded info.

Assuming your userform is functional, after you load it but before you show it, just add If-Then code to compare the value in the cell for the April 3rd date to Now. If the current time (Now) is less than April 3rd then change the enabled property of the option button to true, else to false.

You can also set a button to on, off or enabled at this point, or load a value into a text box before you show the userform.

Hope that helps. I'm using my iPad, otherwise, I might have worked up an example.

**raj.bris** · 03-01-2013, 10:44 PM

Sorry this was my first time ever asking for help online... please see the following code what I have come up with and working so far. I am not very happy with it.
Also, I have changed a bit of cell referencing as well.

A1 is R15 and today() = S15. I hope this makes sense.

Please Login or Register  to view this content.

**raj.bris** · 03-01-2013, 10:54 PM

Hi Evanzo

Sorry to bother you again.

I appreciate your quick response. I think I managed to do excatly what you suggested for locking of option buttons but just wondering how to use current time (now) in userform code.

Cheers

**evanzo** · 03-01-2013, 11:06 PM

http://www.techonthenet.com/excel/formulas/now.php or do a search in excel help

**event21** · 03-01-2013, 11:10 PM

Hi -

seems like this would work for you;

Please Login or Register  to view this content.

event

**raj.bris** · 03-01-2013, 11:31 PM

Thanks Event. It worked pretty well and looks quite nice as well. just wondering if I can use now() rather than CDate() as I would like it to be based on time as well.
Would it be too much to ask?
Cheers

**raj.bris** · 03-01-2013, 11:34 PM

Thanks Evanzo, I am looking into this now. looks like a pretty useful link.

**event21** · 03-01-2013, 11:43 PM

Hi -

CDate includes time as well.

event

Originally Posted by raj.bris

Thanks Event. It worked pretty well and looks quite nice as well. just wondering if I can use now() rather than CDate() as I would like it to be based on time as well.
Would it be too much to ask?
Cheers

**raj.bris** · 03-02-2013, 12:02 AM

perfect... I just realised a second ago when I re-tested.
Great Job Mate.

**raj.bris** · 03-02-2013, 12:05 AM

hi mate.
how do we close this thread?
Cheers

**event21** · 03-02-2013, 12:16 AM

Hi -

It's there.

http://www.excelforum.com/faq.php

event

**raj.bris** · 03-02-2013, 01:05 AM

Thanks to everybody for all the help and guidance.

Disable frames & option buttons on userform based on today's date

LinkBack

Thread Tools

Rate This Thread

Display

Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Re: Disable frames & option buttons on userform based on today's date

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions