Userform Textbox with date adjusted by worksheet cell

wlwebb
wlwebb used Ask the Experts™
on
Hi
I have an Excel 2007 workbook where I have created a Userform.  On that Userform the clerk will select a date from a Calendar Control.  That selected date then populates textbox tbDateEnd with the following

(Note-This code works)
Private Sub Calendar1_Click()
    ActiveCell.NumberFormat = "m/d/yyyy"
    tbDateEnding.Value = Calendar1.Value
    Sheets("History Detail").Range("DateEndingInput").Value = Calendar1.Value
    
End Sub

Open in new window


This following is what I can't get to work

I have a second textbox where I want the deliver date to be calculated and recorded.  The deliver date lead time is a named range on worksheet "Company Data"  That name is "LeadTimeDaysAdjustment" and is a formula that returns a number (example say 7).

What I am trying to do is when the clerk clicks the calendar control it populates that tbDateEnding (which it currently does) and then populates the "tbDeliverDate" with the tbDateEnding + the LeadTimeDaysAdjustment

Here is the code I used for that:
Private Sub tbDateEnding_Change()
    DaysAdjust.Value = Worksheets("Company Data").Range("LeadTimeDaysAdjustment").Value
    ActiveCell.NumberFormat = "m/d/yyyy"
    
    tbDateEnding.Value = (tbDateEnding.Value + DaysAdjust.Value)
    
End Sub

Open in new window


It does not work.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
can you try
tbDeliverDate.Value = (Calendar1.Value + DaysAdjust.Value)
tbDeliverDate.Value = (tbDateEnding.Value + DaysAdjust.Value)

Open in new window

should work as well

Author

Commented:
I keep getting "Object Required" as an Error when it hits the DaysAdjust.Value = Worksheets...........
Analyst Assistant
Commented:
What is DaysAdjust?

Is it a control on the userform or some other object?

Or is it a variable you've declared?

If it's a variable just remove the .Value part.

It might also be worth using DateValue to  return a 'real' date from the text date in the tbDateEnding
Private Sub tbDateEnding_Change()

    DaysAdjust = Worksheets("Company Data").Range("LeadTimeDaysAdjustment").Value
    
    tbDeliverDate.Value = DateValue(tbDateEnding.Value)  + DaysAdjust
    
End Sub

Open in new window



By the way, why do you have this?
ActiveCell.NumberFormat = "m/d/yyyy"

Open in new window

Author

Commented:
Thanks.  Worked when I fixed that issue

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial