UpperSelection Vba opens unwanted “Personal – excel” spreadsheet

[Ken44]

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?

Sub UpperSelection()
            For Each cell In Selection
                        If Not cell.HasFormula Then
                                    Cell.Value = UCase(cell.Value)
                        End if
            Next cell
End Sub

 

[tygrrboi]

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

 

[Ken44]

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