Display cell value in userform label which changes at interval of 24

[eimisQ]

I need date to be displayed in userform label however date must be taken from a cell and must change at 24 mark [ see the image below]

. Please help

https://imgur.com/JZ0cRUD

 

[Kamolga]

Re: How to display cell value in userform label which changes at interval of 24

Hi,

I had issue to find 24:00 so I opted for a >23:59 and it worked

Private Sub UserForm_Activate()
Dim dCell As Range
    For Each dCell In Sheet1.Range("B:B") 'Range must be time range only or add a isdate(dCell)=true
        If dCell > TimeValue("23:59:59") Then
        dCell.Select
        Label1.Caption = CStr(dCell.Offset(0, -1).Value)
        Exit For
        Else
        'Handle Error
        End If
    Next dCell
End Sub

 

[eimisQ]

Re: How to display cell value in userform label which changes at interval of 24

thanks for the replay, but one problem for me is that in "B" column time is a general number and isn't expressed by hours:minutes:seconds

 

[Kamolga]

Re: How to display cell value in userform label which changes at interval of 24

For 24:00 format you should use the custom format with

[LEFT][COLOR=#222222][FONT=Verdana][h]:00 [/FONT][/COLOR][/LEFT]

and it should work perfectly.
Otherwise, write somewhere your value 24:00 in your format in white in a blank cell (let's say A1) and change

[LEFT][COLOR=#333333][FONT=monospace]  If dCell > TimeValue("23:59:59") Then[/FONT][/COLOR][/LEFT]

by

if dCell=Range(A1)

and it should work.
A last option wich was not your question is to use

[LEFT][COLOR=#333333][FONT=monospace]Private Sub UserForm_Activate()

        Label1.Caption = [COLOR=#222222][FONT=Verdana]Cstr(Range("A4").End(xlDown))[/FONT][/COLOR]
End Sub[/FONT][/COLOR][/LEFT]

​

if cells in A:A have no formula. If they have then

[COLOR=#333333][FONT=monospace]Private Sub UserForm_Activate()
Dim I as long
I=5
Do until cells(1,i).value <>""
i=i+1
Loop
        Label1.Caption = [COLOR=#222222][FONT=Verdana]cstr(cells(1,i).value)[/FONT][/COLOR]
End Sub[/FONT][/COLOR]

should also work

<strike></strike>
​

 

[mikerickson]

Re: How to display cell value in userform label which changes at interval of 24

Rather than going through CStr, have you looked at using the Format function.

Label1.Caption = Format(Cells(1,i).Value, "h:mm AM/PM")

 

[Kamolga]

Re: How to display cell value in userform label which changes at interval of 24

Rather than going through CStr, have you looked at using the Format function.

Label1.Caption = Format(Cells(1,i).Value, "h:mm AM/PM")

It is a date, not time (column A) that needs to be in caption, so

[LEFT][COLOR=#222222][FONT=Verdana]Label1.Caption =[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] [/FONT][/COLOR][COLOR=#222222][FONT=Verdana]Format(cells(1,i),"Short Date")[/FONT][/COLOR][/LEFT]

or

[LEFT][COLOR=#222222][FONT=Verdana][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][LEFT][COLOR=#222222][FONT=Verdana]Format(cells(1,i),"dd/mm/yy")[/FONT][/COLOR][/LEFT][/FONT][/COLOR][/LEFT]

.

​

​

Any reason to prefer Format to CStr?

 

[mikerickson]

Re: How to display cell value in userform label which changes at interval of 24

Any reason to prefer Format to CStr?

If you put the date 3/2/2018 in A1. The .Value property will change with the format of the cell.
As will CStr of that value. But Format will work no matter what the user does to the formatting of the Cell.

 

[eimisQ]

Re: How to display cell value in userform label which changes at interval of 24

Hi,

I tried using other methods but I still can't get it to work. https://imgur.com/a/Il7F7aK

 

[Kamolga]

Re: How to display cell value in userform label which changes at interval of 24

 if dCell.value=sheet1.Range("A1").value

neither?

 

[eimisQ]

Re: How to display cell value in userform label which changes at interval of 24

I'm afraid not https://imgur.com/L7lQKDU