{"id":242787,"date":"2022-08-09T21:34:57","date_gmt":"2022-08-09T21:34:57","guid":{"rendered":"https:\/\/vmlogger.com\/excel\/?p=242787"},"modified":"2022-08-17T18:29:59","modified_gmt":"2022-08-17T18:29:59","slug":"changing-case-of-existing-text","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/","title":{"rendered":"VBA Code &#8211; Change the Case  of existing Texts in a Sheet"},"content":{"rendered":"<p>[et_pb_section fb_built=&#8221;1&#8243; admin_label=&#8221;section&#8221; _builder_version=&#8221;4.16&#8243; da_disable_devices=&#8221;off|off|off&#8221; global_colors_info=&#8221;{}&#8221; da_is_popup=&#8221;off&#8221; da_exit_intent=&#8221;off&#8221; da_has_close=&#8221;on&#8221; da_alt_close=&#8221;off&#8221; da_dark_close=&#8221;off&#8221; da_not_modal=&#8221;on&#8221; da_is_singular=&#8221;off&#8221; da_with_loader=&#8221;off&#8221; da_has_shadow=&#8221;on&#8221;][et_pb_row admin_label=&#8221;row&#8221; _builder_version=&#8221;4.16&#8243; background_size=&#8221;initial&#8221; background_position=&#8221;top_left&#8221; background_repeat=&#8221;repeat&#8221; global_colors_info=&#8221;{}&#8221;][et_pb_column type=&#8221;4_4&#8243; _builder_version=&#8221;4.16&#8243; custom_padding=&#8221;|||&#8221; global_colors_info=&#8221;{}&#8221; custom_padding__hover=&#8221;|||&#8221;][et_pb_text _builder_version=&#8221;4.17.6&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221;]In this article, I am going to share a VBA code that can be used to change the cases of texts written in an excel worksheet. This can be used to enforce proper casing on texts written in a sheet or cell range. As you know &#8211; casing can be of 3 types: 1. lower, 2. UPPER or 3. Proper. Using this function you would be able to do any of this.<\/p>\n<h1>Using Excel formula<\/h1>\n<p> You can convert any text to lower, upper or proper cases in excel formula like below:<\/p>\n<ul>\n<li>\n<span class=\"pa-color-highlight\">=LOWER(F9)<\/span><\/li>\n<li>\n<span class=\"pa-color-highlight\">=UPPER(F9)<\/span><\/li>\n<li>\n<span class=\"pa-color-highlight\">=PROPER(F9)<\/span><\/li>\n<\/ul>\n<div id=\"attachment_242794\" style=\"width: 1034px\" class=\"wp-caption aligncenter\"><img data-dominant-color=\"f3f2e0\" data-has-transparency=\"true\" style=\"--dominant-color: #f3f2e0;\" decoding=\"async\" aria-describedby=\"caption-attachment-242794\" src=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/08\/formula-1024x457.png\" alt=\"Casing Formula\" width=\"1024\" height=\"457\" class=\"has-transparency size-large wp-image-242794\" srcset=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/08\/formula-980x437.png 980w, https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/08\/formula-480x214.png 480w\" sizes=\"(min-width: 0px) and (max-width: 480px) 480px, (min-width: 481px) and (max-width: 980px) 980px, (min-width: 981px) 1024px, 100vw\" \/><p id=\"caption-attachment-242794\" class=\"wp-caption-text\">Casing Formula<\/p><\/div>\n<p>Now let&#8217;s do this by VBA code. Using VBA code, you would be able to do it automatically without using any formula.<\/p>\n<h1>Change casing of Texts in a Worksheet<\/h1>\n<p>The following function will be run on the selected area on a worksheet and convert the texts into a cell in lower, upper or proper case.<br \/>\n<span class=\"pa-color-highlight\">Note:<\/span> Following code will ignore those cells which already has formula in it. This is important so that you don&#8217;t mess with the formula in a cell.<\/p>\n<pre>\nSub ChangeCase()\n    Dim cellRange As Range\n    Application.EnableEvents = False\n    For Each cellRange In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells\n       cellRange.Value = StrConv(Rng.Text, vbUpperCase)\n       'Similar to changing UpperCase, you can use \n       'vbLowerCase and vbProperCase for LowerCase and ProperCase respectively\n    Next cellRange\n    'enable vba events back again\n    Application.EnableEvents = True\nEnd Sub\n<\/pre>\n<p>You can also write a VBA code, which can change the case as soon as you type in a cell or cell range. This way, you can enforce user to type it a format you expect them to type. Incase they don&#8217;t type, VBA will automatically correct it in the correct format.<\/p>\n<p>For example, in an Excel Form, if you want user to type their name in all Capital Letters, then you can use the following code in <strong>Worksheet_Change <\/strong>procedure. You are expected to type the Name in <strong>cell A10 <\/strong><\/p>\n<pre>\nPrivate Sub Worksheet_Change(ByVal Target As Range)\n    If Not Application.Intersect(Range(\"A10\"), Target) Is Nothing Then\n        Application.EnableEvents = False\n        If IsNumeric(Target.Value) = False Then\n            Target.Value = StrConv(Target.Text, vbUpperCase)\n        End If\n        Application.EnableEvents = True\n    End If\nEnd Sub\n<\/pre>\n<div class=\"alert alert-success\">\n<h2>Conclusion:<\/h2>\n<hr\/>\n<p>I hope you liked the article and learned something new today. Try this in your application and share your feedback. Thank you so much.<\/p>\n<\/div>\n<p>[\/et_pb_text][et_pb_blurb title=&#8221;You may like these too&#8230;&#8221; use_icon=&#8221;on&#8221; font_icon=&#8221;&#xf034;||fa||900&#8243; _builder_version=&#8221;4.17.6&#8243; _module_preset=&#8221;0249c68e-4de8-4f44-84ff-a9b1850785b6&#8243; hover_enabled=&#8221;0&#8243; global_colors_info=&#8221;{}&#8221; sticky_enabled=&#8221;0&#8243;]<\/p>\n<ol>\n<li><a href=\"https:\/\/vmlogger.com\/excel\/2011\/11\/how-to-force-proper-case-in-textbox\/\" target=\"_blank\" rel=\"noopener\">How to force proper case in textbox<\/a><\/li>\n<li><a href=\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-macro-tutorial-excel-form\/\" target=\"_blank\" rel=\"noopener\">Excel Macro Tutorial : Excel Form<\/a><\/li>\n<\/ol>\n<p>[\/et_pb_blurb][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n<span class=\"et_bloom_bottom_trigger\"><\/span>","protected":false},"excerpt":{"rendered":"<p>In this article, I am going to share a VBA code that can be used to change the cases of texts written in an excel worksheet. This can be used to enforce proper casing on texts written in a sheet or cell range. As you know &#8211; casing can be of 3 types: 1. lower, [&hellip;]<\/p>\n","protected":false},"author":45,"featured_media":242800,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1672,1246],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.9 (Yoast SEO v21.9.1) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>VBA Code - Change the Case of existing Texts in a Sheet - Let&#039;s excel in Excel<\/title>\n<meta name=\"description\" content=\"VBA code to change the case of Texts. Convert Names to a form in capital letters. Restrict users to type in the proper case in excel form.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VBA Code - Change the Case of existing Texts in a Sheet\" \/>\n<meta property=\"og:description\" content=\"VBA code to change the case of Texts. Convert Names to a form in capital letters. Restrict users to type in the proper case in excel form.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/\" \/>\n<meta property=\"og:site_name\" content=\"Let&#039;s excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2022-08-09T21:34:57+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-17T18:29:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/08\/change-case-of-texts.png\" \/>\n\t<meta property=\"og:image:width\" content=\"400\" \/>\n\t<meta property=\"og:image:height\" content=\"250\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"VBA Code &#8211; Change the Case of existing Texts in a Sheet\",\"datePublished\":\"2022-08-09T21:34:57+00:00\",\"dateModified\":\"2022-08-17T18:29:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/\"},\"wordCount\":479,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Formula\",\"Excel Macro\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/\",\"name\":\"VBA Code - Change the Case of existing Texts in a Sheet - Let&#039;s excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2022-08-09T21:34:57+00:00\",\"dateModified\":\"2022-08-17T18:29:59+00:00\",\"description\":\"VBA code to change the case of Texts. Convert Names to a form in capital letters. Restrict users to type in the proper case in excel form.\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Formula\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-formula\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"VBA Code &#8211; Change the Case of existing Texts in a Sheet\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let&#039;s excel in Excel\",\"description\":\"Let&#039;s share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore &amp; currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"VBA Code - Change the Case of existing Texts in a Sheet - Let&#039;s excel in Excel","description":"VBA code to change the case of Texts. Convert Names to a form in capital letters. Restrict users to type in the proper case in excel form.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/","og_locale":"en_US","og_type":"article","og_title":"VBA Code - Change the Case of existing Texts in a Sheet","og_description":"VBA code to change the case of Texts. Convert Names to a form in capital letters. Restrict users to type in the proper case in excel form.","og_url":"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/","og_site_name":"Let&#039;s excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2022-08-09T21:34:57+00:00","article_modified_time":"2022-08-17T18:29:59+00:00","og_image":[{"width":400,"height":250,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/08\/change-case-of-texts.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"VBA Code &#8211; Change the Case of existing Texts in a Sheet","datePublished":"2022-08-09T21:34:57+00:00","dateModified":"2022-08-17T18:29:59+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/"},"wordCount":479,"commentCount":0,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Formula","Excel Macro"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/","url":"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/","name":"VBA Code - Change the Case of existing Texts in a Sheet - Let&#039;s excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2022-08-09T21:34:57+00:00","dateModified":"2022-08-17T18:29:59+00:00","description":"VBA code to change the case of Texts. Convert Names to a form in capital letters. Restrict users to type in the proper case in excel form.","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/changing-case-of-existing-text\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Formula","item":"https:\/\/vmlogger.com\/excel\/excel-formula\/"},{"@type":"ListItem","position":3,"name":"VBA Code &#8211; Change the Case of existing Texts in a Sheet"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let&#039;s excel in Excel","description":"Let&#039;s share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore &amp; currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/242787"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=242787"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/242787\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/242800"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=242787"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=242787"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=242787"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}