Excel Forum

Calendar/Date Picker for Excel 2016 (64 bit) running on Windows 8.1 (64 bit)

Robert Hyde

Active Member

Members

Forum Posts: 3

Member Since:
September 16, 2019

Offline

September 16, 2019 - 2:25 pm

Hi everyone,

First, I am new to this forum, but I hope I can finally find a solution to my problem. I will try to keep it short and to the point.

I am using Windows 8.1 (64 bit) and MS Excel 2016 (also 64 bit).

I am trying to automate an inventory spreadsheet by using a macro input form (userform). The major headache I have is the date picker function. I have spent numerous hours looking for a solution for a Date Picker. I downloaded the date picker xlsm file from the post "JULY 9, 2015 by Philip Treacy"

When I try to use it, I get a 438 run-time error "Object doesn't support this property or method"

I've reduced my worksheet and user form to 2 entries to test if it works; if I comment out the calendar frame line it works

With lastrow

' .Cells(1,1) = Frame1.Value
.Cells(1, 2) = BulkCode.Value

End With

I am not an experienced VBA user, but I use excel a lot to help the employees enter records.

I have attached the excel (xlsm) file and error msgs that I received. Any help from anyone would be fantastic.

Thank you

Bob Hyde

Attachments

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1824

Member Since:
November 8, 2013

Offline

September 16, 2019 - 9:37 pm

Hi Robert,

Should be >Frame1.Caption, not .Value

The .Value property an be used in textbox, combobox, checkbox, but in labels, frames, use .Caption.

But the Caption will return a null string, because the date is not in the Frame, it's the value of the calendar you need.

I's not an error in Date Picker, it's how you use the objects.
Use instead:
With lastrow
.Cells(1, 1) = Calendar1.Value
.Cells(1, 2) = BulkCode.Value
End With

Robert Hyde

Active Member

Members

Forum Posts: 3

Member Since:
September 16, 2019

Offline

September 16, 2019 - 9:48 pm

Catalin,

Thank you so much.

I have tried it out. It seems like the date value only appears when I double click on the day box in the calendar. Also, if my cursor is in another cell on the spreadsheet the date value shows up in that cell example, if my cursor was on D10, and I double click on the day (in the calendar), then the date will show up in cell D10. I want the date only to show up in the date column of the table.....is that possible?

Thank you again

Robert

Robert Hyde

Active Member

Members

Forum Posts: 3

Member Since:
September 16, 2019

Offline

September 16, 2019 - 9:53 pm

Catalin,

My sincere apologies...It works fine

I did not read the bit that you changed in the with lastrow code

Fantastic. You are a scholar and great help.......

Robert

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1824

Member Since:
November 8, 2013

Offline

September 16, 2019 - 11:08 pm

There is a double click event used in that code you have, but it should be removed because it will always write to active cell, you have no control of where the date goes.