VBA code to replace "1" with "7:00/ 15:30"

[GLV]

Hi, everyone,

I am an VBA newbie to say at most. Please help me! I have searched through dozens of post, but nothing worked for me. Or maybe i don't know what to search

I have an excel workbook with more than 2 or 3 sheets. It's an schedule for the place i work at. We are working in shifts: 1st is from 7 to 15:30, 2nd is from 13:30 to 22:00 and 3rd is from 22:00 to 06:30.
As mentioned in the title, i need some code (or any idea) that will automatically replace 1, 2 or 3 with the above hour intervals. But with no macros.
I want it to happen in real time. I type 1, press Enter and poof!, 7:00/ 15:30 appears instead of 1. And so on for 2 and 3.

Thank you all !

[georgiboy]

Welcome to the forum,

What version of Excel do you use?

Also a little confused, in the title you state: "VBA Code to replace"
But then in the main text you state: "But with no macros."

[Aussiebear]

Can you attach a sample workbook so we can understand how and where this procedure is meant to take place?

[georgiboy]

Also if you read the forum rules, i believe you should provide a link when cross posting:
https://www.excelforum.com/excel-pro...0-15-30-a.html

[GLV]

As i said, i am newbie. Barely know a thing or two about VBA.
I attached the spreadsheet in which i need this replacement to take place.
Basically, when i type 1 and press enter, i need it to automatically change into "7:00/ 15:30".
Many thanks !

Welcome to the forum,

What version of Excel do you use?

Also a little confused, in the title you state: "VBA Code to replace"
But then in the main text you state: "But with no macros."

Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit

[p45cal]

Check out the attached; no macro/vba.

[GLV]

Check out the attached; no macro/vba.

The problem i encountered with Custom formatting is that the content can't be displayed if the column has a width of 3.14 (the width i need).

[p45cal]

The problem i encountered with Custom formatting is that the content can't be displayed if the column has a width of 3.14 (the width i need).

I noticed this; even with a linefeed in the formatting (a) autowidth goes to a width as if there was no linefeed and (b) anything less than that width shows ####.
I don't know a work round for that except to use a macro/vba.

[georgiboy]

You could have a key at the side and use, days, lates and nights or even D, L and N. If the times of the shifts are fixed do they need to be in every cell?

[Aussiebear]

Welcome to the forum GLV. Since it's been brought to your attention on both forums, and noticeably you not reacted on either forum re the cross posting issue, I shall bring it to your attention. Cross posting ( that is posting the same issue on another forum) breeches the rules of almost all vba forums that I'm aware of. Please show some respect to those members who volunteer their time to assist. if you are getting some assistance elsewhere then allow us to view that help as it could easily enable us to help you better.

I need a response from you here.

[GLV]

My apologies!
This is the another post:
https://www.excelforum.com/excel-pro...ml#post5806823

[Aussiebear]

Okay, thank you for acknowledging the other postings.

[arnelgp]

try this,
type 1 and it will return: 7:00/ 15:30
type 2 and it will return: 22/ 6:30

[Paul_Hossler]

This will do the 1,2,3 conversion, but the tiny column width is a problem for the longer strings

Capture.JPG

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Dim iLastRow As Long, iLastCol As Long


    Set r = Target.Cells(1, 1)
    
    If Len(r.Value) = 0 Then Exit Sub
    
    With Me
        iLastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
        iLastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
    End With
    
    With r
        If .Row < 4 Or .Row > iLastRow Then Exit Sub
        If .Column < 3 Or .Column > iLastCol Then Exit Sub
        
        Application.EnableEvents = False
        Select Case .Value      '   1st is from     7 to 15:30, 2nd is from 13:30 to 22:00 and 3rd is from 22:00 to 06:30.
            Case 1
                .Value = "7:00/15:30"
            Case 2
                .Value = "13:30/22:00"
            Case 3
                .Value = "22:00/06:30"
        End Select
        Application.EnableEvents = True
    End With

End Sub

[p45cal]

the tiny column width is a problem for the longer strings

Perhaps a variation along the lines of?:

                .Value = "7:00/" & vblf & "15:30"

(untested)

[Paul_Hossler]

@p45cal -- I did tried that but still didn't fit

[Dave]

Maybe...

r.ShrinkToFit = True

Too tiny to see? Dave

[p45cal]

@p45cal -- I did tried that but still didn't fit

It could be down to zoom levels. I tested my suggestion, no changes to column width or font size, then at zoom 100% I got:

2023-03-27_152944.png

and at zoom 130% (nothing else changed) got:

2023-03-27_153013.png

[GLV]

Thanks all for your time!

I finally used this: https://www.excelforum.com/attachmen...v-example.xlsx
It's 6StringJazzer's solution.