1

I have the following code to simulate some cells behave like buttons

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   'Application.ScreenUpdating = False
   If Target.Cells.Count = 1 Then
       '~~~~~~ pseudocode ~~~~~~
       If {select cell is one of the chose ones}
            {do some stuff}
       End If
       '~~~~~~ pseudocode ~~~~~~
   End If

   Range("A1").Select
   'Application.ScreenUpdating = True
End Sub

It doesn't matter whereas I use the ScreenUpdating code, the "Selection box" is flying around from A1 to the selected cell and back and it make the sheet much slower.

Can this flying (animation) selection box be stoped?

So far I have found this, not possible to hide, but noting about the flying efect:

Hide the cell selection box in Excel

Edit:

I need (I think so) edit capabilities on the sheet, therefore the option of not changing selection cell is not applicable. Due to:

  • most of the sheet is informative, and should be available for copy (not edited)

  • some cells are input forms (free text thing), selection as usual

  • some cells should behave like buttons (plus/minus for a numeric value, sclaes, simple stuff, but thousand of then, so much easier do/maintain by code), and user must not edit them

  • grouping should be available, (so that's complicate protecting the sheet)

I am not closed to the option : Range("A1").Select after each (most) of user interaction, but no other method comes into mind to me now.

An example:

enter image description here

I know some would say: "you should make this out from excel", and I agree with you, but this is a mandatory thing, I do not have the power to raise this question

  • As you can see, I got the "flying selection" that I try to get rid off

  • cell A1 is already hodden, that will do most of the trick

  • final version sure will go with hidden gridlines and headlines

  • rows groups exist, and are important, so no protection possible

  • all the functionality, I can do easy with vba, just problem with the animation

PeterT
  • 8,232
  • 1
  • 17
  • 38
nojoxanor
  • 11
  • 3
  • 1
    You should also disable events with `Application.EnableEvents = False`, then re-enable at the end of the sub. – PeterT Sep 10 '19 at 18:40
  • 2
    Is there a reason to select anything? See [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – cybernetic.nomad Sep 10 '19 at 18:54
  • 2
    Don't `Select` stuff, the `Selection` won't be flying around =) – Mathieu Guindon Sep 10 '19 at 19:38
  • 1
    @cybernetic.nomad the reason to "select nothing" is to get out the focus (selection) from the cell that should behave as a button (not allowing changes, etc..) – nojoxanor Sep 10 '19 at 22:20
  • @PeterT, thanks, but this do not avoid the flying around – nojoxanor Sep 10 '19 at 22:20
  • 1
    We're having a difficult time understanding what your real issue is without a clearer explanation of the purpose of the form and the workflow you're trying to establish. A small reproducible example to illustrate what you mean would be perfect. The advice you'll get in response will be much closer to a real solution for you. – PeterT Sep 11 '19 at 12:27
  • @PeterT, thanks, I edit with a simulation and a little explanation – nojoxanor Sep 11 '19 at 16:11
  • @nojoxanor You haven't really explained anything. You keep saying that you *have to* select, but it's not clear why. You don't need to select anything at the beginning of a macro, but you can capture the pre-existing `Selection` in a Range variable as the user input. You don't need to select anything in the middle of a macro at all. You may need to select your A1 in the end of the macro if it is your requirement to have this cell selected at the end, but then it is only one `Select` call. – GSerg Sep 11 '19 at 16:25
  • @nojoxanor, I have to allow the user select cells so I can capture "what he want to do" (and allow to write in the red cells) , I use the select "back to A1" to allow, for example, click twice on the PLUS sign, or to take out the selection after chose a option. – nojoxanor Sep 11 '19 at 17:19
  • Your design looks like you're not using any form controls; all your controls exist as regular cells and you process the user actions when certain cells are selected. Is this correct? If that is correct, then I'm puzzled by the "flying selection" rectangle. Without any controls on the worksheet, the only selections available should be of one or more cells -- therefore the selection rectangle should ONLY show as a border around cells. Yours is clearly floating above the worksheet. This can only really happen if you're using form controls. What controls are you using? – PeterT Sep 11 '19 at 18:34
  • @PeterT, yes, indeed I only used basic-cells, and program them (there is hundrers and adding real-controls will be a pain), and get the by selection change – nojoxanor Sep 11 '19 at 18:40
  • @PeterT, I was happy getting the selection box (printsecreen) as it was flying from A1 to the click on "Send Questionaire" [thanks for the edit and pasting the picture] – nojoxanor Sep 11 '19 at 18:41
  • At this point, I think we'll have to see more code in your `Worksheet_SelectionChange` routine in order to figure out why the `Application.ScreenUpdating` is not solving your problem. – PeterT Sep 11 '19 at 18:51
  • @PeterT, I do not have the code yet, I am just planning. Application.ScreenUpdating does not hide the "flying animation", It helps a lot for the colouring, cells updates, calculations etc... – nojoxanor Sep 11 '19 at 19:01
  • @PeterT, my only issue, is with the flying animation, back and forth, if I do not want the focus to keep in the selected cell bu the user – nojoxanor Sep 11 '19 at 19:02

2 Answers2

0

Maybe this is not the answer that you have been waiting for, but as Mathieu mentioned in his comment, please try to avoid using Selection.

It does make things slower and often causes errors (in example try selecting cell from hidden sheet). Instead just do something with the range that you define with your if statements directly. Every property of Cell or Range can be accessed directly.

Hope it helps.

Mateusz_G
  • 36
  • 4
  • Hi, thanks for the Application.EnableEvents = False but behiviour keeps the same – nojoxanor Sep 10 '19 at 22:17
  • and sorry I need to "select", as I need to allow users to click on some cells (free-text edits) and on others that will look like buttons (but I do not want to left selection on those) – nojoxanor Sep 10 '19 at 22:18
  • Can I get the "selected cell" before the selection occurs in some way? ( i am thinking like Java events) – nojoxanor Sep 10 '19 at 22:22
0

Not sure how you can achieve you "flying select box" problem, but at least you could add this code, so opening/closing groups are available on protected sheets:

'Password Protect Current Sheet
ActiveSheet.Protect Password:="Add_here_your_password", UserInterfaceOnly:=True

'Enable Group Collapse/Expand Capabilities
ActiveSheet.EnableOutlining = True

How about trying to remove the "back to A1" as much as possible?

Maybe do it only on absolutely necessary, or move back to the changed value (the 33 in your example), or to the question title (in your multi-option example)

pGrnd2
  • 514
  • 1
  • 5
  • 14