Accumulative date stored in a caption

[dpaton05]

I have 14 boxes for a calander and above the each box is a label. I want to put the date of the first day in the fortnight above the caption of the first label. I then want it to auto fill to the other labels to add one day for each label so the date above each box represents the dates in the fortnight, starting from the date I enter above the first box. How would I do this?

 

[Special-K99]

By boxes you mean cells? Cells on a spreadsheet? Or boxes on a form?
Assuming you're putting a date in cell A1 and the cells continue on the same row...


in B1
=A1+1
copy along until row N

 

[dpaton05]

I have an idea. I will enter the date in a cell below the boxes. I then copy them to a cell above the first box. From there, I can get the cells behind each box to be the right date but how do I get the label to be the value of the cell? I can't just use the cell as the label as the beginning of the cell does not coincide with the beginning of the box.

I am pretty sure I would need some vba here but I have no idea what.

Thanks,
Dave

 

[Special-K99]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[MickG]

Assuming you have Userform with your Labels and "TextBoxes" on.
Perhaps this:-
NB: This code goes in the Userform Code Module.

When you show your userform you will get an "Input box" for your first date, then as the Userform loads the subsequent Labels will get their incremented dates.

Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] MyDt [COLOR="Navy"]As[/COLOR] Date
MyDt = InputBox("Enter Date", "Date", "Type your Date here")
[COLOR="Navy"]If[/COLOR] IsDate(MyDt) [COLOR="Navy"]Then[/COLOR]
    Me.Label1.Caption = MyDt
    [COLOR="Navy"]For[/COLOR] n = 2 To 14
        MyDt = DateAdd("d", 1, MyDt)
        UserForm1.Controls("Label" & n).Object.Caption = MyDt
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick