0

Is there any way to remove the small, red circle indicating today in the MonthView control? I've been doing a bit of googling, and the closest I've found is this, which seems to contain a solution, but in VB6, not VBA. Furthermore, having a look at the various files uploaded to that post, I have trouble understanding which part is removing the circle, nevermind if it is possible to use the same solution in Excel-VBA.

Any input on whether what I am hoping to do is at all possible would be much appreciated.

Community
  • 1
  • 1
eirikdaude
  • 3,106
  • 6
  • 25
  • 50
  • 1
    That is what I call "A Blast from the Past" :D Yes you can use APIs to achieve that but you may want to see [This](http://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba/12013961#12013961). This is a much simpler alternative. – Siddharth Rout Jun 30 '15 at 08:10
  • @SiddharthRout Thanks for the suggestion :) I was kinda aware of the alternative, I have used [this user-crafted control](https://sites.google.com/site/e90e50/calendar-control-class) on occassion. I was hoping to keep things simple this time round though, without having to incorporate any extra modules or classes, but if the other alternative is starting to mess around with APIs it may be simpler to copy in a different control instead. – eirikdaude Jun 30 '15 at 12:31

1 Answers1

1

It's pretty simple using API:

Add this to the top of your UserForm:

Private Declare Function GetWindowLong Lib "user32" _
    Alias "GetWindowLongA" ( _
    ByVal hWnd As Long, _
    ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" _
    Alias "SetWindowLongA" ( _
    ByVal hWnd As Long, _
    ByVal nIndex As Long, _
    ByVal dwNewLong As Long) As Long

Private Const GWL_STYLE As Long = -16
Private Const MCS_NOTODAYCIRCLE As Long = 8

And use this to remove it: 

Private Sub UserForm_Initialize()

  With Me.MonthView1
    SetWindowLong .hWnd, GWL_STYLE, GetWindowLong(.hWnd, GWL_STYLE) Or MCS_NOTODAYCIRCLE
  End With

End Sub
joehanna
  • 1,471
  • 1
  • 11
  • 22
  • Using this solution I get a compile error on the line `.SetWindowLong...`, "Method or data member not found". I assume that means the function `SetWindowLong` doesn't exist in my user32-library? – eirikdaude Aug 04 '15 at 09:03
  • You might need to change the `With Me.MonthView1` to match the name of the control on your form. Another consideration could be whether you are using 32 bit or 64 bit Excel. – joehanna Aug 04 '15 at 09:25
  • Yeah, I already changed the name of the control and I am running Excel 2013, 32-bit version. However, I had a look at [Microsoft's support-page for SetWindowLong](https://msdn.microsoft.com/en-us/library/windows/desktop/ms633591%28v=vs.85%29.aspx) and it seems they recommend using pointer-function instead? I assume the only thing I'd have to do is add in the "pointer" in the declarations, etc, to use that instead? Finally I don't quite get what the `Or`-statement in your function does - would you be kind enough to try to explain that? – eirikdaude Aug 04 '15 at 09:32
  • Sorry, I've edited the answer. `SetWindowLong` should not have had the dot in-front of it. As for `SetWindowLongPtr`, this is for 64 bit compatibility, which is why I asked about 32/64 bit Excel. The `SetWindowLong` function is setting the style of the Month Calendar Control. The constant `MCS_NOTODAYCIRCLE` is in charge of the Today Circle. However, it is in the negative ("NOTtodaycircle"). Using `Or` will turn this bit on and therefore remove the circle. See https://msdn.microsoft.com/en-us/library/windows/desktop/bb760919(v=vs.85).aspx for more info on styles for Month Calendar Control. – joehanna Aug 06 '15 at 00:39
  • Thanks, that worked like a charm, though I feel that I should have noticed that typo myself. I ended up modifying your code slightly, as I am not entirely sure if all the computers who end up using the workbook will be win32. Found some useful information [here](http://www.mrexcel.com/forum/excel-questions/808075-please-help-visual-basic-applications-compatibility-between-32-bit-64-bit-versions.html#post3950161), and I also had to learn a bit about [conditional compilation](https://msdn.microsoft.com/en-us/library/9ae6e432%28v=vs.90%29.aspx?f=255&MSPPError=-2147217396), which was enlightening. – eirikdaude Aug 06 '15 at 12:23
  • In the end the function code for the userform looked [like this](http://pastebin.com/1p6btrqv). Feel free to include however much you want of the above into your answer, at any rate I'll mark it as the solution to my problem, as it was what brought me to the result I wanted. – eirikdaude Aug 06 '15 at 12:23