Date property in Macro use..

[omairhe]

Could anyone please guide me as to how to insert the date as the max value in the spin button. For example I have this code

Sub DateValue()
Dim shp As Excel.Shape
Set shp = ActiveSheet.Shapes("sbDate")
With shp.ControlFormat
    .Min = 0
    .Max = 0
End With
End Sub

The .Max value should be equal to what I get from the formula =DAY(TODAY())-1
The formula will output the current date with one day minus to the result. For instance today is 11th of December, 2020 . The formula will output 11-1 = 10
So 10 is what I need to set in Max value.

Anyone?

 

[RoryA]

.Max = Day(Date) - 1

should work.

 

[omairhe]

.Max = Day(Date) - 1

should work.

Thank you. This worked.