Cell content is moved automatically to same cell on another sheet

Question

There's a bug or error in the code or workbook somewhere but I can't seem to find it or understand why is this happening.

What's happening is There's a userform which has a button to :

1. Select a cell from sheet2
2. Put the selected cell value in sheet1 cell B10 which is a dropdown menu
3. Unloads the form return to sheet1 and select a cell which is described in the code as sheet1.B26 now .

Now as soon as the form is gone. If I click in the sheet1.B26 selected cell and write something press {TAB} the content from that cell is transferred to the exact same cell but on sheet2 automatically and sheet1.B26 cell is empty .

This happens only once if I immediately start writing in the selected cell when the form is gone.

There is no code/formula in the worksheet or workbook or module that should describes this action.

This is bugging me for a week but I couldn't find solution anywhere.

Workbook if you wish to download it and try it.

Video Description of the error

To recreate this:

1. Go to sheet1
2. Fire up the form using very first button on sheet1 which has "Klant zoek" text on it.
3. Press the button which say "selecteer" on the form.
4. Without selecting any other cell Enter something in last selected cell by the macro and press tab.

P.S. - I didn't write any of the code in that workbook I am just assigned to debug it.

Answer 1

I had a similar issue with an Excel solution I built a while ago that was shared across countries, the issue in this case was with R1C1 address notations and I think it may be the same here, but it can't be easily tested from one local machine, read and try the below.

To clarify, R1C1 address notation refers to row and column by name, so cell C4 has an R1C1 address notation of R4C3

The specific issue I had involved the use of INDIRECT, for example =INDIRECT("'Sheet1'!R21C10", FALSE)

This worked for a number of the countries but failed for those where 'Row' did not begin with an 'R' and 'Column' did not being with a 'C'.

Nederlands is 'Rij' and 'Kolom' so the notation had to be R21K10 for it to work. I.e. =INDIRECT("'Sheet1'!R21K10", FALSE)

The current list I have for this is: -

Language    Row     Column  Full Terms
Deutsch     Z       S       Zeile-Spalte
English     R       C       Row-Column
Español     F       C       Fila-Columna
Français    R       C       Rangée-Colonne
Italiano    F       C       Fila-Colonna
Nederlands  R       K       Rij-Kolom
Polski      R       K       Rząd-Kolumna
Português   L       C       Linha-Coluna
Türkçe      S       S       Sira-Sütun

This issue was only experienced in formulas and not via VBA, however code found in Blad1 (Facturen en Offertes)1 > betalingstermijn (as an example) uses VBA to set a formula in a cell, so the issues align in this respect.

The changes that would be needed would be to change all lines that place R1C1 notation into a formula to the required column initial, examples below.

English (No Change)     
Range("B48").FormulaR1C1 = "=VLOOKUP(R18C2,Bedrijfsgegevens!R[1]C:R47C25,16,FALSE)"

Español (R to F, C no change)
Range("B48").FormulaR1C1 = "=VLOOKUP(F18C2,Bedrijfsgegevens!R[1]C:F47C25,16,FALSE)"

Français (No Change)
Range("B48").FormulaR1C1 = "=VLOOKUP(R18C2,Bedrijfsgegevens!R[1]C:R47C25,16,FALSE)"

Nederlands  (R no change, C to K)
`Range("B48").FormulaR1C1 = "=VLOOKUP(R18K2,Bedrijfsgegevens!R[1]C:R47K25,16,FALSE)"

NOTE: You would not be changing the command name .FormulaR1C1, just the value passed into it.

You can isolate this to test by doing the following in a new sheet.

1. Enter the below data, ending in 4 populated cells

(Sample table)

 |  A  |   B   |
1|First|Second |
2|Third|Forth  |

2. In an empty cell enter the formula =INDIRECT("R1C1",FALSE) (Include the double quotes)

In English this will result in 'First' in Netherlands (Dutch) this will show #REF.

This may not be the only issue but it is one I've encountered in the past and owing to the difficulty in reproducing the issue, I would recommend trying to rule it out at least.

Answer 2

The issue is that it only looks as the sheet "Facturen en Offertes"-B26 is active. It is actually "Klanten"-B26 that is active. It is solved by adding "vbModeles" to the call to the user form.

Sub zoekklant()
    Zoekklantform.Show vbModeless
End Sub

Answer 3

The problem described in question can be easily reproduced in Excel 2013, it doesn't occur in earlier versions. I did not have a chance to test it in 2016. Similar problem can be found here and here.

There are 3 parts common to all cases:

1) UserForm is shown using Form Button or Shape click event handler

2) UserForm is modal (vbModal is default value of UserForm.Show parameter)

3) UserForm code changes active sheet

As a result, sheet activated by UserForm is shown, but it is not really active. Any edits made to it appear in previously (before UserForm was shown) active sheet.

It is a bug in Excel 2013, the easiest workarounds to solve it are:

1) Use ActiveX Button to show the form

2) Show modeless form: UserForm1.Show vbModeless

But what if we cannot use ActiveX and need modal form? This answer suggests hiding and showing ActiveWindow, but it did not work for me.

The only workaround to show modal form without ActiveX working for me is:

To show modal UserForm1, create another empty UserForm2 with Activate event handler:

Private Sub UserForm_Activate()
  'hide form
  Me.Left = -1000
  'show target form 
  UserForm1.Show
  Unload Me
End Sub

And instead of:

UserForm1.Show

Use:

UserForm2.Show vbModeless 

Answer 4

I've experienced a similar issue before as a result of creating a new sheet. In my situation, it had to do with excel having two sheets simultaneously selected, and as such it always chose to write on the earliest one in the sequence until I had fiddled around selecting different sheets to reset the selection. My solution was to simply have the sub select a different sheet and then reselect the desired sheet, resetting the double selection issue.