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

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 https://www.excelforum.com/images/smilies/frown.gif

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 !

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."

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

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

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 !

Quote:

---

Originally Posted by 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."

---

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

Check out the attached; no macro/vba.

Quote:

---

Originally Posted by p45cal

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).

Quote:

---

Originally Posted by GLV

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.

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?

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.

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

Okay, thank you for acknowledging the other postings.

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

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

Attachment 30672

Code:

---

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

---

Quote:

---

Originally Posted by Paul_Hossler

the tiny column width is a problem for the longer strings

---

Perhaps a variation along the lines of?:

Code:

---

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

---

(untested)

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

Maybe...

Code:

---

r.ShrinkToFit = True

---

Too tiny to see? Dave

Quote:

---

Originally Posted by Paul_Hossler

@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:

Attachment 30678

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

Attachment 30679

Thanks all for your time!

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