1

I have made this simple invoice control worksheet in excel and I use VBA to make it easier to visualize and add new items. I have made dozens of other VBA programmed Worksheets, and all of them have a "New" active x button, programmed just like this:

Private Sub ButtNew2_Click()

Dim Guia As Worksheet
Dim UltLin As Integer

Set Guia = Workbooks("Notas Fiscais.xlsm").Worksheets("Saída")
UltLin = Guia.UsedRange.Rows.Count + 1

Guia.Application.Goto Reference:="R" & UltLin & "C1"
FormNotasSaida.Show
FormNotasSaida.BoxDataEmiss.SetFocus

End Sub

Simple as that. Just select the first blank line so that the form loads blank. It works fine in any other Workbook. But in this one, if and every time I click this button, after closing the form, the next time (and only once) I load the form again in any possible way (either double clicking an item, pressing the "Show" button or pressing the "New" button again), it loads either blank or showing the last launched item (case you did so).

After closing it, I can click wherever or press the "Show" button whenever, they work fine, as they always have. The problem occurs exclusively once, after pressing the "New" button.

What am I possibly doing wrong, specially knowing that this method works perfectly in all other workbooks?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Alekos Mazzolotti
  • 15
  • 3
  • Check your UltLin var, this could have stopped at a blank cell, and then gone to next line. – Nathan_Sav Nov 01 '16 at 15:28
  • 1
    Use a generated instance of the form instead of the default instance. See [this Documentation topic](http://stackoverflow.com/documentation/vba/5351/user-forms/19036/best-practices#t=201611011528290148872) for more information. – Comintern Nov 01 '16 at 15:29
  • 1
    FWIW you're *not* selecting the "first blank line". `Guia.UsedRange.Rows.Count + 1` isn't doing what you think it does, and it's not reliable either: [readme](http://stackoverflow.com/a/11169920/1188513). – Mathieu Guindon Nov 01 '16 at 15:57

1 Answers1

3
FormNotasSaida.Show
FormNotasSaida.BoxDataEmiss.SetFocus

Forms are a special kind of class modules with a designer and a predeclared ID attribute. This "predeclared ID" is what's at play here: you're using/reusing the default global instance of the class, and [unless you close it with the X button in the control box,] never unload it so you're always showing the same form.

A best practice is to create a new instance of the form every time you use it:

Set frm = New FormNotasSaida
frm.Show
frm.BoxDataEmiss.SetFocus
Graham
  • 7,431
  • 18
  • 59
  • 84
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235