UpperSelection Vba opens unwanted “Personal – excel” spreadsheet

Ken44

New Member
Joined
Dec 15, 2016
Messages
2
I created the macro in Excel 365 / Windows 10. The macro changes the words in the selection to UPPER CASE, that’s the good news, but then it opens a new spreadsheet (Personal – excel) which I then have to close. Tis quite annoying. I used Bill Jelen’s youtube demo (https://www.youtube.com/watch?v=rrKuasJcmug) to create the macro but it does not operate the same as his. The code I used is shown below. What would you recommend so I stay in the same spreadsheet after the words are changed to upper case?
Code:
Sub UpperSelection()
            For Each cell In Selection
                        If Not cell.HasFormula Then
                                    Cell.Value = UCase(cell.Value)
                        End if
            Next cell
End Sub
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

tygrrboi

Well-known Member
Joined
Sep 8, 2015
Messages
1,196
There is nothing in the provided code which would cause a new spreadsheet to open or change focus...

Perhaps you have something in a worksheet module or in the ThisWorkbook module?

Is this all the code that you use or is there more code elsewhere? (sorry, I cant sit through a youtube video to see what Bill Jelen says....)


Also a side note... I suggest using an innocent variable name like "c" or "piece" instead of "cell". (and declaring it: Dim c as Range) I always get nervous when my variable names sound like they might be built-in VBA language for some other process....
 
Last edited:

Ken44

New Member
Joined
Dec 15, 2016
Messages
2
Thanks for your quick reply and I understand about not wanting to sit through the youtube video. I have been using Kutools for excel to change between upper and lower case, but it requires several clicks to accomplish what Bill showed could be accomplished with a single click. Your thoughtful comments got me thinking about how these macros work. This particular macro must be saved in the Personal Macro Workbook (a new experience for me) and I am now sure this is why the Personal spreadsheet is popping up when I use the macro. I have discovered a simple workaround – instead of closing the Personal spreadsheet, I simply “minimize” it so it is out of the way. Now when I use the Upper or Lower case icons I get the result I want and don’t have to deal with the “popup”. I can live with that. Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,950
Members
414,350
Latest member
ajesh27

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top