0

My original problem was: VBA do not make any computation during the run (copying, pasting, applying formulas) even when calculations are automatically defined, and even when screenUpdating is True then since I export out a PDF it seems to be with missing data. Only until I close the userform it makes all computations. So a trick was unload Userform1 which works but is odd.

Private Sub CommandButton1_Click()

'some code for button1 on userform1 which should be already executed when I hit button2

End Sub

Public Sub CommandButton2_Click()
'this sub routine hide userform1 to userform2 and make more calculations

If UserForm1.ComboBox1.ListIndex <> -1 Then
'TextBox16.Value = ListBox2.Column(0, ListBox2.ListIndex)


Dim hFile As Workbook
Set hFile = ThisWorkbook
Dim rng1, rng2, rng3, rng4, rng44, rng5, rng6, rng7, rng8 As Range
Dim rngPM, rngDM, rngSE, rngH, rngW As Range

Set rng1 = hFile.Sheets("Promedio-Movil").Range("D6:D17") 'real 2019
Set rngPM = hFile.Sheets("Promedio-Movil").Range("I6") 'real ene 20
Set rng2 = hFile.Sheets("Movil-Doble").Range("D6:D17") 'real 2019

UserForm1.Hide
UserForm2.Show 'HERE THE PROBLEM, THIS SHOULD BE AT THE START

ActiveWorkbook.Sheets("Datos").Select

If UserForm1.ComboBox1.ListIndex = 0 Then
'Aguja-Espinal'

Range("B3:B14").Copy
rng4.PasteSpecial Paste:=xlPasteValues ' real 2018 holt

End If
'some more code to print PDF

End Sub

The problem it's precisely that in the code above, I was using set and other codes before I change from userform1 to userform2 to say I did the change in the middle of the code, but changing to the start now works as expected.

'some more code using set here
Set rng8 = hFile.Sheets("DescomposicionSeries").Range("C2:C9")


UserForm1.Hide
UserForm2.Show 'these two lines to the start of the Sub routine
Valerin
  • 133
  • 1
  • 6
  • I already tried ```Application.ScreenUpdating = True`` but the whole macro make all steps until I close the userform, but I need it before, otherwise the computations are not include in the pdf exported. – Valerin Jul 06 '21 at 00:58
  • As a test to see if it just doesn't have time to "propagate", try a delay before exporting? 'Application.Wait(Now + TimeValue("00:00:02"))' will give a 1 to 2 second delay. If this does work, I'm not sure what you can do to work around the delay except for trying smaller delays. (For accurate delays see https://stackoverflow.com/a/1544582/3654325 and associated answers) – Stax Jul 06 '21 at 01:29
  • Make sure calculation is on automatic or run `hFile.Sheets("Regresion").Calculate` before printing. If events are involed a `DoEvents` might help too. – Pᴇʜ Jul 06 '21 at 06:30
  • @Pᴇʜ I already tried, also wait with 59 seconds, but still the same. – Valerin Jul 06 '21 at 15:20
  • Clutching at straws now, but maybe try Unloading the forms once you're finished with them? E.g. 'Unload UserForm1' – Stax Jul 08 '21 at 00:58
  • @Stax Yes Thanks I did this at first and works! But I aready found the original problem! I was doing some computations and in the middel of... I did ```userform1.hide userform2.show``` so that was the problem. Moving those line to the begining solved the issue. – Valerin Jul 08 '21 at 14:16

0 Answers0