VBA is skipping code when a userform reference is reached? Attempting to write to another wbk

0
votes

I'm using a userform to collect data and add it to an empty line in a workbook. Structure of code is as follows:

  1. Main sub s_OpenWriteToTargetFile is called from userform mainForm.
  2. It checks availability of the target workbook.
  3. It opens the target workbook.
  4. It calls sub "s_WriteLines". Everything is OK up to this point.
  5. Sub s_WriteLines should load textbox values from mainForm into various variables and paste them into the target workbook.

For some reason, code execution jumps out of s_WriteLines as soon as it reaches With MainForm..., and it returns to the mother sub.

s_WriteLines sub 

Sub s_WriteLines  
    Dim a,b as integer    

    With mainForm    
        a = .tb_a.Value    
        b = .tb_b.Value    
    End With    
End Sub

I can't wrap my head around it. Does this have something to do with the modality of the userform?

1
vbaexceluserform
tried to check if the values in both tb_a & tb_b are integers? - Romcel Geluz
@RomcelGeluz Yes, I double checked. I ran the code step by step and it doesn't even get into "with mainform...". Code above the "with mainform" will run ok. - 00253
just a note (not the solution): Dim a,b as integer only declares b As Integer but leaves a As Variant you need to specify a type for every variable. - Pᴇʜ
tried passing the mainForm into a variable as Object type? Like, Dim objThis as object: set objThis = mainForm. - Romcel Geluz
I would suspect an unfortunate combination of having an On Error Resume Next somewhere in the caller module AND not having mainForm as a valid object. Put this before With mainForm to check: On Error Goto 0 : Debug.Print mainForm.Caption - AcsErno

1 Answers

1
votes

As AcsErno suggested in the comments, there was a on error resume next that I didn't notice, and it kept me from learning that the form is failing to load a rowsource property of a combobox.

The rowsource was specified as follows:
mainForm.cb_Wiresize.RowSource = wiresizesWSheet.Name & "!" & wiresizesFinalRange.Address

The workbook that is opened to be written in also becomes active, and then the range that I specified as rowsource refers to a worksheet that doesn't exist - because I specified it only as "worksheet + range", instead of "workbook + worksheet + range".

To expand on my question, how can I refer to the specific workbook object using the syntax posted above? I tried different formulations but none worked.