Why do you think "it didn't take"?
It shows up here just fine for me, where you've had a response:
Unselect/Deselect ALL cells with vba
This is a discussion on Unselect/Deselect ALL cells with vba within the Excel Questions forums, part of the Question Forums category; IF this is a duplicate post I apologize. I thought I just posted it but it seems it didn't take. ...
IF this is a duplicate post I apologize. I thought I just posted it but it seems it didn't take.
I may have asked this question a few years ago but I lost the answer.
When choosing a sheet tab activating the sheet, I don't want any cell to be selected/activated as would be evidenced by a cell 'selection cursor' being visible on the last cell the user picked during the last visit to that particular tab (sheet). So, nothing is hilighted/selected until a user actually picks a cell. (This is how a new sheet appears before a user has picked a cell for the first time.)
VBA is no problem.
I can't seem to find a method that more or less deselects/unselects all cells in a sheet.
It appeared that the first post didn't take because I stopped midstream when I realized I needed to update my profile for a new email address. After updating address, I submitted the post. When I returned to the forum, the post wasn't there. This post is a differently worded version of the first post which didn't seem to appear in the forum. Clear?
BTW, thank you for your reply. It also seems that I confused the issue about the topic of the post. I believe I was thinking about the process of unhiliting after a copy/paste selection and confusing that with wanting to hide the current cell position in a sheet.
Now that you straightened me out, this post topic is solved.
However, regarding posting, I thought the first post was applied to this forum area. I don't see which forum area that first 'missing' post was submitted to although you have it. Where did I post it?
I am a beginner macro/VBA programmer.
I have created a macro (associated with a button) that forces a formula to generate a new random number. It does this by invoking the “calculate” command (or whatever you call it VBA speak – does not seem like an object or a method). Here is the macro:
Private Sub CommandButton1_Click()
Once this macro is done running, there are no cells selected.
I have another macro (also associated with a button) in the same sheet that hides and unhides a row. Once the macro has been run, I don’t want the row or any cell to be selected (as is the case after the first macro above runs). I can assure that the row is not selected by adding a line to actively select a cell or range, but I haven’t been able to find a way to code it so no cells are selected. Here is the macro in its current form:
Application.Calculation = xlManual
.EntireRow.Hidden = Not .EntireRow.Hidden
Please let me know if you have a suggestion for how to code the macro so no cells are selected after a row is hidden or unhidden.
The reason that your Calculate macro leaves no cells selected is that CommandButton1 stays selected after it is clicked,
since the macro doesn't have any statements that select cells on the worksheet.
You would get the same effect with a procedure with no statements, like...
If you wanted to apply that concept to your macro, you add a last step that selects a shape in your worksheet.Code:Private Sub CommandButton1_Click() End Sub
The shape could be a rectangle for example that you could place in a hidden row or column.
A simpler approach might be to have your code select a Cell that isn't currently displayed on the screen,Code:Sub Button4_Click() Application.Calculation = xlManual With ActiveSheet.Rows("9") .EntireRow.Hidden = Not .EntireRow.Hidden .Parent.Shapes("Rectangle 1").Select End With End Sub
then scroll back to the saved display.
Code:Sub SelectCellOutsideDisplay() Dim lScrollRow As Long, lScrollCol As Long Application.ScreenUpdating = False On Error Resume Next With ActiveWindow lScrollRow = .ScrollRow lScrollCol = .ScrollColumn With .VisibleRange .Offset(.Rows.Count + 10, _ .Columns.Count + 10).Select End With .ScrollRow = lScrollRow .ScrollColumn = lScrollCol End With Application.ScreenUpdating = True End Sub
Using Excel 2010
Another way is to make the command button NOT become selected.
Go to Properties window of the Command Button. (Design mode->Right click button-->pProperties)
Set to False.Code:TakeFocus*******
The asterix above are On Click (without space).
Somehow the Forum sftware is converting On Click without space to ******!
Last edited by drsarao; Nov 12th, 2011 at 05:17 AM.
Excel 2007 Windows 7
I believe AChrisA's question is how to make it so there are no worksheet cells selected after running the hide/show row 9 macro.
I only explained about the selection of the button, because AChrisA was looking for a way to repeat the effect observed when running the Calculate macro, as a way to have no worksheet cells selected.
Using Excel 2010
Thanks JS411 !
What do I need to do to find existing macro-button shape names?
Thx again for your help!
If you select the form button shape throught the macro, it highlights it as if it were in edit mode. This doesn't look very good, which is why I suggested selecting a rectangle shape in a hidden cell.
Would the SelectCellOutsideDisplay macro work for your situation?
That seems the better approach to me.
Using Excel 2010