Workbook to open to a specific sheet and select cell with today's date

[Greep3r]

Hi

I have a question about the below code:

Private Sub Workbook_Open()    Dim CellToShow As Range
    Worksheets("Smart City Projects Schedule").Select
    x = DateTime.Date
    Set CellToShow = Worksheets("Smart City Projects Schedule").Rows(6).Find(What:=x, LookIn:=xlValues)
    If CellToShow Is Nothing Then
        MsgBox "No Cell for day " & x & " found.", vbCritical
    Else
        With CellToShow
            .Select
            .Show 'Scroll the window to show the cell
        End With
    End If
End Sub

Upon opening the workbook the correct sheet within the workbook but the only condition where the correct cell with today's date is chosen is if I have the cell format set to "yyyy/mm/dd".

This would not be a problem but seeing that this is a ghantt chart with a huge amount of rows I need to keep the column width as small as possible. When I change the cell format to only show "dd" I get an error that the value cannot be found. If I make the column width smaller to show "##" I get the same error message of value not found. The only condition where the code works properly is if the date is fully visible and set to "yyyy/mm/dd".

Any suggestion how this can be addressed to work properly?

Regards

 

[dmt32]

Hi,
as an idea, try changing this line

 Set CellToShow = Worksheets("Smart City Projects Schedule").Rows(6).Find(What:=x, LookIn:=xlValues)

to this

 Set CellToShow = Worksheets("Smart City Projects Schedule").Rows(6).Find(What:=x, LookIn:=xlFormulas, lookat:=xlWhole)

and see if this will do what you want when cell formatted to display your dates as "dd"

Dave

 

[Greep3r]

Hi

Thanks for the suggestion. I got it to work with your suggested changes.

Regards

 

[dmt32]

Hi

Thanks for the suggestion. I got it to work with your suggested changes.

Regards

Glad suggestion helped - many thanks for feedback


Dave