Excel VBA “Unselect” wanted
I would like the final appearance after my VBA has finished running to be empty of selection -- to have no cell or range on any sheet colored (if it was range selected) or in a bold line box (anything that was selected). Pupose: to present the client with a neat final appearance.
I've searched and cannot find how to do this. There is an Unselect according to MS, but it doesn't seem to do anything.
microsoft-excel vba
add a comment |
I would like the final appearance after my VBA has finished running to be empty of selection -- to have no cell or range on any sheet colored (if it was range selected) or in a bold line box (anything that was selected). Pupose: to present the client with a neat final appearance.
I've searched and cannot find how to do this. There is an Unselect according to MS, but it doesn't seem to do anything.
microsoft-excel vba
Why don't you select cell A1 when you save to present a clean look. If you want to strip cell formatting that is a different thing.
– wbeard52
Sep 4 '12 at 0:09
The cursor has to be somewhere. Why not place it bottom right withWorksheets("xxxx").Cells(Rows.Count,Columns.Count).Select
or anywhere that is at least a screen away from the used area.
– Tony Dallimore
Sep 4 '12 at 8:16
could you just place it back where it was when the macro was called?
– SeanC
Sep 4 '12 at 15:45
add a comment |
I would like the final appearance after my VBA has finished running to be empty of selection -- to have no cell or range on any sheet colored (if it was range selected) or in a bold line box (anything that was selected). Pupose: to present the client with a neat final appearance.
I've searched and cannot find how to do this. There is an Unselect according to MS, but it doesn't seem to do anything.
microsoft-excel vba
I would like the final appearance after my VBA has finished running to be empty of selection -- to have no cell or range on any sheet colored (if it was range selected) or in a bold line box (anything that was selected). Pupose: to present the client with a neat final appearance.
I've searched and cannot find how to do this. There is an Unselect according to MS, but it doesn't seem to do anything.
microsoft-excel vba
microsoft-excel vba
asked Sep 3 '12 at 22:07
Gary
36112
36112
Why don't you select cell A1 when you save to present a clean look. If you want to strip cell formatting that is a different thing.
– wbeard52
Sep 4 '12 at 0:09
The cursor has to be somewhere. Why not place it bottom right withWorksheets("xxxx").Cells(Rows.Count,Columns.Count).Select
or anywhere that is at least a screen away from the used area.
– Tony Dallimore
Sep 4 '12 at 8:16
could you just place it back where it was when the macro was called?
– SeanC
Sep 4 '12 at 15:45
add a comment |
Why don't you select cell A1 when you save to present a clean look. If you want to strip cell formatting that is a different thing.
– wbeard52
Sep 4 '12 at 0:09
The cursor has to be somewhere. Why not place it bottom right withWorksheets("xxxx").Cells(Rows.Count,Columns.Count).Select
or anywhere that is at least a screen away from the used area.
– Tony Dallimore
Sep 4 '12 at 8:16
could you just place it back where it was when the macro was called?
– SeanC
Sep 4 '12 at 15:45
Why don't you select cell A1 when you save to present a clean look. If you want to strip cell formatting that is a different thing.
– wbeard52
Sep 4 '12 at 0:09
Why don't you select cell A1 when you save to present a clean look. If you want to strip cell formatting that is a different thing.
– wbeard52
Sep 4 '12 at 0:09
The cursor has to be somewhere. Why not place it bottom right with
Worksheets("xxxx").Cells(Rows.Count,Columns.Count).Select
or anywhere that is at least a screen away from the used area.– Tony Dallimore
Sep 4 '12 at 8:16
The cursor has to be somewhere. Why not place it bottom right with
Worksheets("xxxx").Cells(Rows.Count,Columns.Count).Select
or anywhere that is at least a screen away from the used area.– Tony Dallimore
Sep 4 '12 at 8:16
could you just place it back where it was when the macro was called?
– SeanC
Sep 4 '12 at 15:45
could you just place it back where it was when the macro was called?
– SeanC
Sep 4 '12 at 15:45
add a comment |
8 Answers
8
active
oldest
votes
Select any cell and turn off CutCopy
:
Range("A1").Select
Application.CutCopyMode = False
Yup, perfect for me. Thanks. I had a VBA macro that copied some cells from one workbook to another and then closed the source workbook, but because some cells were still selected for copy in the source it wouldn't close right away. Application.CutCopyMode = False fixed that for me. Thanks bud!
– TKoL
Dec 29 '16 at 10:10
add a comment |
There is a tricky way to do it.
Create an object such as a button. Select this button, then hide it, and no cell will be selected.
ActiveSheet.Shapes("Button 1").Visible = True
ActiveSheet.Shapes("Button 1").Select
ActiveSheet.Shapes("Button 1").Visible = False
That's it.
add a comment |
The only answer is to cheat.
Hide column A
and row 1
Put cursor in A1
. There will be a tiny dot in the corner of B2
or you could select a cell outside of the viewable range.
– KronoS
Sep 5 '12 at 18:21
@KronoS, which at 10% zoom, and a 1280x1024 screen is ~HA375. perhapsXFD1048576
? :)
– SeanC
Sep 5 '12 at 18:25
add a comment |
Excel always has something selected. A work around is needed. Selecting a cell off screen will set focus there, so that won't work in and of itself. This code places the cursor off screen and then scrolls the sheet back up to view A1
.
Sub NoSelect()
Range("BB100").Select
ActiveWindow.SmallScroll up:=100
ActiveWindow.SmallScroll ToLeft:=44
End Sub
If you are really wanting 'nothing selected`, you can use VBA to protect the sheet at the end of your code execution, which will cause nothing to be selected. You can either add this to a macro or put it into your VBA directly.
Sub NoSelect()
With ActiveSheet
.EnableSelection = xlNoSelection
.Protect
End With
End Sub
As soon as the sheet is unprotected, the cursor will activate a cell.
add a comment |
Depending on the size of your selection, to get rid of any artifacting (I don't know if this is also an issue in 2013 Excel, but on Mac it was a constant pain for me) you can just loop through cell by cell and select each.
add a comment |
By turning off screen updating before selecting a cell, scroll to selected cells is temporarily disabled.
This code checks which cells are currently visible and selects the first cell below the visible range which is not in view.
Eg, when i try it: Visble range is A1:BC79, so this code selects A80 and scrolling down and to the right and running it again reveals the excel chooses the first cell NOT visible below the first visible column.
Dim r As Range
Application.ScreenUpdating = False
Set r = Application.ActiveWindow.VisibleRange
r(r.Cells.Count + 1).Select
Application.ScreenUpdating = True
add a comment |
Very old question, but my answer for reference:
You can use
With ActiveSheet
.EnableSelection = xlNoSelection
.Protect
End With
add a comment |
Use a trick: Add a shape, then select it and hide it.
Source code for this is in answer of different question on this site.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f469720%2fexcel-vba-unselect-wanted%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
8 Answers
8
active
oldest
votes
8 Answers
8
active
oldest
votes
active
oldest
votes
active
oldest
votes
Select any cell and turn off CutCopy
:
Range("A1").Select
Application.CutCopyMode = False
Yup, perfect for me. Thanks. I had a VBA macro that copied some cells from one workbook to another and then closed the source workbook, but because some cells were still selected for copy in the source it wouldn't close right away. Application.CutCopyMode = False fixed that for me. Thanks bud!
– TKoL
Dec 29 '16 at 10:10
add a comment |
Select any cell and turn off CutCopy
:
Range("A1").Select
Application.CutCopyMode = False
Yup, perfect for me. Thanks. I had a VBA macro that copied some cells from one workbook to another and then closed the source workbook, but because some cells were still selected for copy in the source it wouldn't close right away. Application.CutCopyMode = False fixed that for me. Thanks bud!
– TKoL
Dec 29 '16 at 10:10
add a comment |
Select any cell and turn off CutCopy
:
Range("A1").Select
Application.CutCopyMode = False
Select any cell and turn off CutCopy
:
Range("A1").Select
Application.CutCopyMode = False
edited Nov 25 '13 at 15:16
Andrea
1,43631316
1,43631316
answered Nov 25 '13 at 13:31
user276802
9112
9112
Yup, perfect for me. Thanks. I had a VBA macro that copied some cells from one workbook to another and then closed the source workbook, but because some cells were still selected for copy in the source it wouldn't close right away. Application.CutCopyMode = False fixed that for me. Thanks bud!
– TKoL
Dec 29 '16 at 10:10
add a comment |
Yup, perfect for me. Thanks. I had a VBA macro that copied some cells from one workbook to another and then closed the source workbook, but because some cells were still selected for copy in the source it wouldn't close right away. Application.CutCopyMode = False fixed that for me. Thanks bud!
– TKoL
Dec 29 '16 at 10:10
Yup, perfect for me. Thanks. I had a VBA macro that copied some cells from one workbook to another and then closed the source workbook, but because some cells were still selected for copy in the source it wouldn't close right away. Application.CutCopyMode = False fixed that for me. Thanks bud!
– TKoL
Dec 29 '16 at 10:10
Yup, perfect for me. Thanks. I had a VBA macro that copied some cells from one workbook to another and then closed the source workbook, but because some cells were still selected for copy in the source it wouldn't close right away. Application.CutCopyMode = False fixed that for me. Thanks bud!
– TKoL
Dec 29 '16 at 10:10
add a comment |
There is a tricky way to do it.
Create an object such as a button. Select this button, then hide it, and no cell will be selected.
ActiveSheet.Shapes("Button 1").Visible = True
ActiveSheet.Shapes("Button 1").Select
ActiveSheet.Shapes("Button 1").Visible = False
That's it.
add a comment |
There is a tricky way to do it.
Create an object such as a button. Select this button, then hide it, and no cell will be selected.
ActiveSheet.Shapes("Button 1").Visible = True
ActiveSheet.Shapes("Button 1").Select
ActiveSheet.Shapes("Button 1").Visible = False
That's it.
add a comment |
There is a tricky way to do it.
Create an object such as a button. Select this button, then hide it, and no cell will be selected.
ActiveSheet.Shapes("Button 1").Visible = True
ActiveSheet.Shapes("Button 1").Select
ActiveSheet.Shapes("Button 1").Visible = False
That's it.
There is a tricky way to do it.
Create an object such as a button. Select this button, then hide it, and no cell will be selected.
ActiveSheet.Shapes("Button 1").Visible = True
ActiveSheet.Shapes("Button 1").Select
ActiveSheet.Shapes("Button 1").Visible = False
That's it.
edited Sep 4 '14 at 13:37
HopelessN00b
1,81931829
1,81931829
answered Sep 4 '14 at 13:06
user364941
211
211
add a comment |
add a comment |
The only answer is to cheat.
Hide column A
and row 1
Put cursor in A1
. There will be a tiny dot in the corner of B2
or you could select a cell outside of the viewable range.
– KronoS
Sep 5 '12 at 18:21
@KronoS, which at 10% zoom, and a 1280x1024 screen is ~HA375. perhapsXFD1048576
? :)
– SeanC
Sep 5 '12 at 18:25
add a comment |
The only answer is to cheat.
Hide column A
and row 1
Put cursor in A1
. There will be a tiny dot in the corner of B2
or you could select a cell outside of the viewable range.
– KronoS
Sep 5 '12 at 18:21
@KronoS, which at 10% zoom, and a 1280x1024 screen is ~HA375. perhapsXFD1048576
? :)
– SeanC
Sep 5 '12 at 18:25
add a comment |
The only answer is to cheat.
Hide column A
and row 1
Put cursor in A1
. There will be a tiny dot in the corner of B2
The only answer is to cheat.
Hide column A
and row 1
Put cursor in A1
. There will be a tiny dot in the corner of B2
answered Sep 5 '12 at 17:56
SeanC
3,28411425
3,28411425
or you could select a cell outside of the viewable range.
– KronoS
Sep 5 '12 at 18:21
@KronoS, which at 10% zoom, and a 1280x1024 screen is ~HA375. perhapsXFD1048576
? :)
– SeanC
Sep 5 '12 at 18:25
add a comment |
or you could select a cell outside of the viewable range.
– KronoS
Sep 5 '12 at 18:21
@KronoS, which at 10% zoom, and a 1280x1024 screen is ~HA375. perhapsXFD1048576
? :)
– SeanC
Sep 5 '12 at 18:25
or you could select a cell outside of the viewable range.
– KronoS
Sep 5 '12 at 18:21
or you could select a cell outside of the viewable range.
– KronoS
Sep 5 '12 at 18:21
@KronoS, which at 10% zoom, and a 1280x1024 screen is ~HA375. perhaps
XFD1048576
? :)– SeanC
Sep 5 '12 at 18:25
@KronoS, which at 10% zoom, and a 1280x1024 screen is ~HA375. perhaps
XFD1048576
? :)– SeanC
Sep 5 '12 at 18:25
add a comment |
Excel always has something selected. A work around is needed. Selecting a cell off screen will set focus there, so that won't work in and of itself. This code places the cursor off screen and then scrolls the sheet back up to view A1
.
Sub NoSelect()
Range("BB100").Select
ActiveWindow.SmallScroll up:=100
ActiveWindow.SmallScroll ToLeft:=44
End Sub
If you are really wanting 'nothing selected`, you can use VBA to protect the sheet at the end of your code execution, which will cause nothing to be selected. You can either add this to a macro or put it into your VBA directly.
Sub NoSelect()
With ActiveSheet
.EnableSelection = xlNoSelection
.Protect
End With
End Sub
As soon as the sheet is unprotected, the cursor will activate a cell.
add a comment |
Excel always has something selected. A work around is needed. Selecting a cell off screen will set focus there, so that won't work in and of itself. This code places the cursor off screen and then scrolls the sheet back up to view A1
.
Sub NoSelect()
Range("BB100").Select
ActiveWindow.SmallScroll up:=100
ActiveWindow.SmallScroll ToLeft:=44
End Sub
If you are really wanting 'nothing selected`, you can use VBA to protect the sheet at the end of your code execution, which will cause nothing to be selected. You can either add this to a macro or put it into your VBA directly.
Sub NoSelect()
With ActiveSheet
.EnableSelection = xlNoSelection
.Protect
End With
End Sub
As soon as the sheet is unprotected, the cursor will activate a cell.
add a comment |
Excel always has something selected. A work around is needed. Selecting a cell off screen will set focus there, so that won't work in and of itself. This code places the cursor off screen and then scrolls the sheet back up to view A1
.
Sub NoSelect()
Range("BB100").Select
ActiveWindow.SmallScroll up:=100
ActiveWindow.SmallScroll ToLeft:=44
End Sub
If you are really wanting 'nothing selected`, you can use VBA to protect the sheet at the end of your code execution, which will cause nothing to be selected. You can either add this to a macro or put it into your VBA directly.
Sub NoSelect()
With ActiveSheet
.EnableSelection = xlNoSelection
.Protect
End With
End Sub
As soon as the sheet is unprotected, the cursor will activate a cell.
Excel always has something selected. A work around is needed. Selecting a cell off screen will set focus there, so that won't work in and of itself. This code places the cursor off screen and then scrolls the sheet back up to view A1
.
Sub NoSelect()
Range("BB100").Select
ActiveWindow.SmallScroll up:=100
ActiveWindow.SmallScroll ToLeft:=44
End Sub
If you are really wanting 'nothing selected`, you can use VBA to protect the sheet at the end of your code execution, which will cause nothing to be selected. You can either add this to a macro or put it into your VBA directly.
Sub NoSelect()
With ActiveSheet
.EnableSelection = xlNoSelection
.Protect
End With
End Sub
As soon as the sheet is unprotected, the cursor will activate a cell.
answered Sep 5 '12 at 18:38
CharlieRB
20.4k44490
20.4k44490
add a comment |
add a comment |
Depending on the size of your selection, to get rid of any artifacting (I don't know if this is also an issue in 2013 Excel, but on Mac it was a constant pain for me) you can just loop through cell by cell and select each.
add a comment |
Depending on the size of your selection, to get rid of any artifacting (I don't know if this is also an issue in 2013 Excel, but on Mac it was a constant pain for me) you can just loop through cell by cell and select each.
add a comment |
Depending on the size of your selection, to get rid of any artifacting (I don't know if this is also an issue in 2013 Excel, but on Mac it was a constant pain for me) you can just loop through cell by cell and select each.
Depending on the size of your selection, to get rid of any artifacting (I don't know if this is also an issue in 2013 Excel, but on Mac it was a constant pain for me) you can just loop through cell by cell and select each.
answered Feb 22 '15 at 19:00
Jason Pevitt
111
111
add a comment |
add a comment |
By turning off screen updating before selecting a cell, scroll to selected cells is temporarily disabled.
This code checks which cells are currently visible and selects the first cell below the visible range which is not in view.
Eg, when i try it: Visble range is A1:BC79, so this code selects A80 and scrolling down and to the right and running it again reveals the excel chooses the first cell NOT visible below the first visible column.
Dim r As Range
Application.ScreenUpdating = False
Set r = Application.ActiveWindow.VisibleRange
r(r.Cells.Count + 1).Select
Application.ScreenUpdating = True
add a comment |
By turning off screen updating before selecting a cell, scroll to selected cells is temporarily disabled.
This code checks which cells are currently visible and selects the first cell below the visible range which is not in view.
Eg, when i try it: Visble range is A1:BC79, so this code selects A80 and scrolling down and to the right and running it again reveals the excel chooses the first cell NOT visible below the first visible column.
Dim r As Range
Application.ScreenUpdating = False
Set r = Application.ActiveWindow.VisibleRange
r(r.Cells.Count + 1).Select
Application.ScreenUpdating = True
add a comment |
By turning off screen updating before selecting a cell, scroll to selected cells is temporarily disabled.
This code checks which cells are currently visible and selects the first cell below the visible range which is not in view.
Eg, when i try it: Visble range is A1:BC79, so this code selects A80 and scrolling down and to the right and running it again reveals the excel chooses the first cell NOT visible below the first visible column.
Dim r As Range
Application.ScreenUpdating = False
Set r = Application.ActiveWindow.VisibleRange
r(r.Cells.Count + 1).Select
Application.ScreenUpdating = True
By turning off screen updating before selecting a cell, scroll to selected cells is temporarily disabled.
This code checks which cells are currently visible and selects the first cell below the visible range which is not in view.
Eg, when i try it: Visble range is A1:BC79, so this code selects A80 and scrolling down and to the right and running it again reveals the excel chooses the first cell NOT visible below the first visible column.
Dim r As Range
Application.ScreenUpdating = False
Set r = Application.ActiveWindow.VisibleRange
r(r.Cells.Count + 1).Select
Application.ScreenUpdating = True
edited Apr 11 '16 at 7:55
Prasanna
3,07722138
3,07722138
answered Apr 11 '16 at 7:18
Tobias Carlén
111
111
add a comment |
add a comment |
Very old question, but my answer for reference:
You can use
With ActiveSheet
.EnableSelection = xlNoSelection
.Protect
End With
add a comment |
Very old question, but my answer for reference:
You can use
With ActiveSheet
.EnableSelection = xlNoSelection
.Protect
End With
add a comment |
Very old question, but my answer for reference:
You can use
With ActiveSheet
.EnableSelection = xlNoSelection
.Protect
End With
Very old question, but my answer for reference:
You can use
With ActiveSheet
.EnableSelection = xlNoSelection
.Protect
End With
answered Dec 20 '18 at 12:44
Joost
4526
4526
add a comment |
add a comment |
Use a trick: Add a shape, then select it and hide it.
Source code for this is in answer of different question on this site.
add a comment |
Use a trick: Add a shape, then select it and hide it.
Source code for this is in answer of different question on this site.
add a comment |
Use a trick: Add a shape, then select it and hide it.
Source code for this is in answer of different question on this site.
Use a trick: Add a shape, then select it and hide it.
Source code for this is in answer of different question on this site.
edited May 23 '17 at 12:41
Community♦
1
1
answered Oct 11 '16 at 16:29
miroxlav
7,38342467
7,38342467
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f469720%2fexcel-vba-unselect-wanted%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Why don't you select cell A1 when you save to present a clean look. If you want to strip cell formatting that is a different thing.
– wbeard52
Sep 4 '12 at 0:09
The cursor has to be somewhere. Why not place it bottom right with
Worksheets("xxxx").Cells(Rows.Count,Columns.Count).Select
or anywhere that is at least a screen away from the used area.– Tony Dallimore
Sep 4 '12 at 8:16
could you just place it back where it was when the macro was called?
– SeanC
Sep 4 '12 at 15:45