Manage "empty" Dates From DTPicker Control

May 20, 2006

I'm having a problem with a form where I have created several DatePicker controls.
Once the user select the control and select a date, it's saved on a cell (all the data is stored as a record, including dates). However, the user could not select a date, and, in this case, I should record nothing on that cell. What I have done is to enable the checkbox property.. so I assumed that the date is valid if this checkbox is checked (however, I don't know how to validate if this checkbox is checked or not). The checkbox property for the date picker is a yes/no value that just show the control as enabled/disabled whether you check or not that checkbox.

Question I have is: How can I do to see if I should be saving a date or not on the cell? I have tried different formulas without success. Sometime works and sometime no. I'm using the following code to validate if the user has selected a date:

If IsNull(DTPicker18.Value) = False Then
Worksheets("DAT").Cells(nPubRow, 37).Value = DTPicker18.Value
Else
' If the user deselected the checkbox I should delete the cell content...
Worksheets("DAT").Cells(nPubRow, 37).Delete
End If

What happens is that sometimes this condition returns True.. but others it returns a 0:0:00 value that is not null.

View 2 Replies


ADVERTISEMENT

DTpicker Value To Cell From Multipage Control

Sep 22, 2007

I have a DTPicker on a multipage form on a userform with the following codes:

sheet1. range("Ac2").value=DTPicker1.value

DTPicker1.value=sheet1.range("AC2").value

The first line of code is in the DTPicker1_change event.

The second line of code is in the userform_initialize event.

This works fine if I move the DTPicker control onto the userform itself, but if I put the control on the multipage form it fails with the following message:
Run Time Error 35788

An error occurred in a call to the Windows date & time picker control.

View 4 Replies View Related

Pass Control Dates To Cells As Dates Not Text

Feb 13, 2008

I have a WAY to complicated excel form that I'm going to move to Access but I need to have this working in the interim. The form has a dynamic element, the user can choose the number of items they are ordering and it unhides the required number of text boxes. To keep the scripting down a did a little work-around to update the database using a loop through the form controls:

'Save the main row
MainOrderRow = ActiveCell.Row
LastRow = MainOrderRow + (ExtraOrders - 1) ' Calculate the last row
ExtraOrderNo = 1

For DBRow = MainOrderRow To LastRow

' Save the text box name with the extra order number
k = "txtDescriptionMul" & ExtraOrderNo & ""
l = "txtQuantityMul" & ExtraOrderNo & ""
m = "txtTotalValueMul" & ExtraOrderNo & ""
n = "ComboBoxUnitMul" & ExtraOrderNo & ""...................

View 2 Replies View Related

Dtpicker In Multipage

Mar 7, 2007

In a Multipage form, the first page contains 2 DTPickers. These show the date value from the specified excel worksheet cells and update the cells accordingly if the user changes the dates with the TDPicker. These work perfectly.

If I place another DTPicker control on another page of the Multipage though, it leads to a run-time error.

As an example, the code for 1 of the successful DTPickers on the first page:

Private Sub DTPickerAccountsStartDate_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
End Sub

Private Sub UserForm_Initialize()
Application. ScreenUpdating = False
ActiveWorkbook.Sheets("Cover").Activate
DTPickerAccountsStartDate.Value = Format(Range("B6").Value, "dd/MM/yyyy")
End Sub

There are no errors in any of the cell references etc and the formatting of the DTPicker controls is exactly the same in all cases.

View 6 Replies View Related

Set DTpicker To Todays Date

Dec 17, 2007

My datepicker automatically defaults to the date when it was first set up. I would like it to always show the current date when the user form is first opened...

View 6 Replies View Related

Limiting Calendar Control Dates

Jan 3, 2012

I have a spreadsheet that will always have the dates needed in cells H40 and H42. I would like the calendar to utilize the data validation in the cell to only allow someone to choose a date that is between the dates in H40 and H42.

Code:
Private Sub Calendar1_Click()
'ActiveSheet.Unprotect Password:="pbrmeasap"
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
Calendar1.Visible = False

[Code] ...

Could I add this code anywhere to make it work?

Code:
Sub DataValidation()
With Selection.Validation
.Delete
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$H$40", Formula2:="=$H$42"

[Code] ......

View 4 Replies View Related

Use One (1) Calendar Control To Add Different Dates To Different Cells

Oct 8, 2006

Is it possible to use one (1) calendar control to add different dates to different cells?

View 5 Replies View Related

Merging MonthView & DTPicker Value Into One Cell

Sep 1, 2009

I have a user form on the attached example and on the form i have 'Month View' calendar control and 'Date/Time Picker'.

I am trying to merge the valuses of both controls into one cell. I have tried doing this with the following

View 9 Replies View Related

DTPicker: Redo The Same Thing Over And Over Again When Others Opens

Feb 21, 2007

i implement DTPicker inside a userform in my pc ... but when i open the userform in other pc there's an error saying could not found DTPicker ... when i reopen it in the pc that i've made the userform the DTPicker is no longer there and i had to redo the same thing over and over again when others opens it in other pc.

View 4 Replies View Related

Selecting Multiple Dates Using Calendar Control

May 2, 2006

how to multiple dates can be selected using the calendar control object? I haven't actually explored this for very long

View 5 Replies View Related

Determine If Calendar Control Date Is Between 2 Dates

Apr 15, 2008

I have a user form that has a calendar button. Once clicked it updates a txtbox on my user form. What I would also like is for another txtbox (txtQtrLeave) to be auto populated based on this entry. Below is the code I've tried:

If CalendarEnd.Calendar1.Value > #3/31/2008# And CalendarEnd.Calendar1.Value < #1/6/2008# Then
Qtr = 1
frmAddClient.txtQtrLeave.Value = Qtr

... Qtr 2, 3 etc

It seems to return a value if I have one criteria but it doesn't work once I add the And element. What am I doing wrong (this has been driving me mad all morning)?

View 2 Replies View Related

Keep/Retain Values Of Dtpicker Controls & Read Back

Sep 9, 2006

In a userform I have 6 DTPlcker controls for 3 periods (from...to... X3). The dates chosen are not saved unfortunately and once I exit and retrieve the file again all the dates in the 6 DTpickers get the date of today.

View 7 Replies View Related

MonthView Control Can Be Set To Allow Users To Select Multiple Ranges And Enter Different Dates

Dec 4, 2013

If MonthView control can be set to allow users to select multiple ranges and enter different dates into those ranges? I know I was able to do with with the previous Datepicker control and I thought it was as easy as setting the Show Modal property to true.

View 1 Replies View Related

Code That Manage Add-ins

Feb 17, 2007

Is there a code that manage Add-ins?

View 5 Replies View Related

Manage Multiple Checkboxes

Feb 23, 2007

I'm having problem in managing 4000 checkboxes in a sheet. My sheet containt 2000 rows with 2 coloum of checkboxes. The question is, how to know when checkbox tick in one coloum then it will automatically tick the other one. I know how to do it with small quantity of checkboxes (i.e.: 10 checkboxes). But 4000 checkboxes make me headache.

View 4 Replies View Related

Using Select Case To Manage Production

May 21, 2014

I'm trying to use a Select Case to log production time-tables.

I've tried a couple angles - one being Select Case.

Every time I run the Macro I get the default ERROR "Wrong Model Entered...."

[Code] .....

I'm thinking the problem lies within

[Code] .....

but I'm not certain.

View 1 Replies View Related

Formula To Manage Output Orientation

Feb 9, 2010

Assuming I’ve the following sample data and wishing if the output data could be formatted using some excel formulas, refer to below sample.

Sample data (Sheet1):
StudentA | Lesson1
StudentA | Lesson21
StudentA | Lesson10
StudentA | Lesson5
StudentB | Lesson5
StudentB | Lesson6
StudentC | Lesson9
StudentC | Lesson8
StudentC | Lesson9
StudentC | Lesson15
StudentC | Lesson11
StudentC | Lesson30
StudentC | Lesson13
StudentD | Lesson1
StudentD | Lesson2
StudentD | Lesson3
StudentD | Lesson4
StudentD | Lesson5
StudentD | Lesson6
StudentD | Lesson7
StudentD | Lesson8

Sample output (Sheet2):
StudentA | StudentB | StudentC | StudentD
Lesson1 | Lesson5 | Lesson9 | Lesson1
Lesson21 | Lesson6 | Lesson8 | Lesson2
Lesson10..................Lesson9 | Lesson3
Lesson5....................Lesson15 | Lesson4
.................................Lesson11 | Lesson5
.................................Lesson30 | Lesson6
.................................Lesson13 | Lesson7
..................................................Lesson8

The sample data could be long and a lot, so it's best if the formula can be made flexible to cater for this requirement.

Please refer to the attached file for some sample data.

View 9 Replies View Related

Manage File With Bunch Of Tabs?

Mar 5, 2013

I just inherited an excel spreadsheet that has 10 tabs per every month in the year. Right now it is not that bad as there are only two months in the spreadsheet but I can see this being a headache to tab through later on in the year.

My initial thought is to make a table of contents worksheet that has a bunch of buttons (with macros) to hide/show tabs based on month. (Note: It would not make sense to consolidate the month worksheets into one as each worksheet already has a lot of data in it to begin with.

View 2 Replies View Related

IF-formula- IF Function Only Manage 7 Commands

Jun 16, 2009

I would like to make an IF-formula in excel. The problem is that i would like to to it for 12 different commands. i.e "IF(A1=14;243;"") but for 12 commands I saw that the in-built IF function in excel only manage 7 commands, is this true?
And how do I write the formula,

View 9 Replies View Related

Manage Addin Behaviour Via Registry

Mar 14, 2007

Is it possible to make a setting in the registry, so next time excel opens
the addin is not "marked" in the addins-list. I can't do it manually because, because the close_event in the addin will be executed and that will screw things up.
Currently I'm looking at the regkeys: addin-manager and options, I see a change in the registry if I close XL when I unchecked an addin from the addin-list. It looks promising, but Iám not familiar with it

View 3 Replies View Related

Inputbox Button Control + Msgbox For Empty Inputbox

Jun 30, 2009

I have the inputbox so i can set a string value,
When the inputbox Cancle button is pressed i want to exit sub,
If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname.
If the inputbox has a value do >>>>>>That>>>>>

View 6 Replies View Related

Insert, Read And Manage Comments Fields

Jun 20, 2006

I know how to insert, read and manage comments fields in Excel, however I want to be able to refer to a comment in another spreadsheet. For example

Spreadheet One contains a series of figures, against one of which a comment has been attached.

Spreadsheet Two has links pulling information from cells in various spreadsheets including the cell in Spreadsheet One which has the comment.

View 3 Replies View Related

Count Of Dates Less Than Current &amp; Corresponding Cells Aren't Blank/Empty

Apr 19, 2008

I have a formula '= COUNTIF(A1:A5,"<"&TODAY())' that counts the number of expired dates in one column. I want to include a second condition to this formula that will check if another column of dates are blank then they should not be counted. So if for example a1:a2 have expired dates, and b1:b2 have both got dates then they should be included in the count.

View 9 Replies View Related

Spreadsheet To Manage My Players In A Virtual Sports Game

Dec 13, 2007

I have been working on a spreadsheet to manage my players in a virtual sports game. I have worked out that the optimum skill scores for all the positions and want a way to compare a player to the optimum.

For example:

1st2nd3rd4th5th6th7th
Position 147.1737.6742.0533.8313.6711.3317.33

These are the averages for Position 1

How do I find the player who closest matches this optimum. Players have scores in the same 7 skill areas.

View 9 Replies View Related

How To Manage New Year File Name Change When Linked To External Workbooks?

Nov 12, 2009

I have a set up a workbook which is linked to numerous other workbooks to provide a summary of information in one location.

However, when we reach April the names of all the linked worksheets will change from 'name 09 - 10' to 'name 10 - 11'.

What is the best way of handling this transition, will I have to manually change all of the links?

I have attached a much simplified example, unfortunately I cannot post the original due to sensitive data.

Apologies if the explanation isnt clear enough, let me know and I'll give further detail.

View 2 Replies View Related

Avoid Hard Coding Control Name Inside Control Event Procedure?

Mar 4, 2014

Is there any way in VBA to refer to a control in its own event procedure without referring to it by name/hard-coding?

It might be clearer to explain by a dummy code example:

[Code] ......

I'm seeking what I would need to replace Line1 with.

View 11 Replies View Related

Select Next Control / Cell On Worksheet After Enter In Control

Jan 9, 2008

Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?

View 9 Replies View Related

Manage A List- List Full With Numbers In A Column

Nov 29, 2007

1- I have a list full with numbers in a column. And I want to count how many digits are there in every cells. If digit of the number is less than 7, macro will complete it to 7 digits by adding 0 (zero) at the beginning of the number.

Example: A1 = 85468... A1 value is 5 < 7 in this case our number will be 0085468 (total 7 digits)

PS: sometime, when numbers start with 0 (zero), excel delete all 0 value at the beginning of the numbers. Macro should prevent this error.

2- I have another list with 2 columns which arranged in order. I mean:
A1=xx1, B1=xx2, A2=xx3, B2=xx4, A3=xx5, B3=xx6... etc. One is at A column, next number in B column.

My request is I want to make this list 1 column as arranged.
A1=xx1, A2= xx2, A3=xx3, A4=xx4... etc.

3- Macro will add a new line to the end of the above list with this format:
Z00001xxxx

Z00001 is static
xxxx is number of the full cells of A column plus 1. It will be 4 digits. If count is less than 4, it will complete it to 4 digits.
Example: if there are 15 cells in the list, last line will Z00010016 (15 + 1)

View 9 Replies View Related

Drag From Treeview Control To Spreadsheet Control

Jan 12, 2007

I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.

I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.

I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).

View 4 Replies View Related

Determine Active Control On Multipage Control

Oct 4, 2007

How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved