Change Landline number and Mobile number format

[mgesh2002]

Hello Ninjas,

In a Range(A1:A10), user may type landline number (8 digits) or mobile number (9 digits). I need a vba code to change the format of the numbers as mentioned below.

If it is a 8 digit (Landline) number then the format should be "5443 8450"
If it is a 9 digit (Mobile) number then the format should be "0 403 806 605"

Also, if they enter less than 8 or more than 9 digit, we can show an error message
"The value in this cell does not appear to be a full valid phone number. Please enter again."

I tried writing a code using Worksheet_change event, but not successful.

Thanks for your help!

Muru

 

[Khalid NGO]

Hi,
VBA necessary ?

as this can be done with CF and DV without VBA.

Regards,

 

[Khalid NGO]

See the attached example

 

[mgesh2002]

Thank you Khalid, It works perfectly fine.

By the way, CF is Conditional formatting and what is DV stands for?

 

[Khalid NGO]

Thank you Khalid, It works perfectly fine.

By the way, CF is Conditional formatting and what is DV stands for?

You are welcome Magesh,
DV stands for Data Validation...

 

[mgesh2002]

Thanks Khalid!

I have another query. Please find attached the excel file. On Row 50, I have two buttons to Add or Delete the primary contact section.

If I press the "Click to add another Primary Contact" button, then the empty Primary Contact section (Row 41:48) inserted below the first one and If I press the "Click to delete new Primary Contact", then the latest Primary Contact should be deleted.

If you could assist me on writing a VBA code for these two buttons that would be really helpful.

Thanks in advance.

Regards,
Muru

 

[Khalid NGO]

Thanks Khalid!

I have another query. Please find attached the excel file. On Row 50, I have two buttons to Add or Delete the primary contact section.

If I press the "Click to add another Primary Contact" button, then the empty Primary Contact section (Row 41:48) inserted below the first one and If I press the "Click to delete new Primary Contact", then the latest Primary Contact should be deleted.

If you could assist me on writing a VBA code for these two buttons that would be really helpful.

Thanks in advance.

Regards,
Muru

oops sorry Muru, I am a vba blind.
hope someone will help you.

Regards,

 

[mgesh2002]

That's fine, Khalid.
I will post a new thread for this request.

Thanks,
Murugesh