VBA Userform with inactive workbook

[dado6612]

Hello all,
I have an Userform which starts up when Workbook is open, and the workbook gets minimized.
Userform is show modeless so other open workbooks could be handled

Private Sub Workbook_Open()ActiveWindow.WindowState = xlMinimized
Workbooks("New BOM Maker Userform.xlsm").Activate
UserForm1.Show vbModeless
End Sub

The Userform consist of a MultiPage with a few tabs, on which each has some comboboxes, textboxes and listboxes
They are all connected to the workbook one way or another, either getting the value from a cell with a formula, or putting a value in other cell, getting rowsourse from named range, handling the ranges via copying, etc..

An example of one of such codes

Private Sub ComboBox3_Change()
With Workbooks("Userform.xlsm")
Workbooks("Userform.xlsm").Sheets("Order").Range("B4") = Me.ComboBox3.Value
If ComboBox3 = "Split" Then
ComboBox10.Visible = True
ComboBox11.Visible = True
Me.Width = 495
MultiPage1.Width = 475
ComboBox4.Value = "8"
Else
ComboBox10 = ""
ComboBox11 = ""
ComboBox10.Visible = False
ComboBox11.Visible = False
MultiPage1.Width = 372
Me.Width = 395
If ComboBox3 = "Offset" Then
    ComboBox8 = ""
    ComboBox10 = ""
    ComboBox11 = ""
    ComboBox5 = "Offset"
Else
ComboBox9 = ""
ComboBox10 = ""
End If
End If
Sheets("Order").Calculate
Me.ComboBox4.RowSource = Workbooks("Userform.xlsm").Sheets("Order").Range("D5").Value
Me.ComboBox5.RowSource = Workbooks("Userform.xlsm").Sheets("Order").Range("D7").Value
TextBox3 = Workbooks("Userform.xlsm").Sheets("Order").Range("b14").Value
End With
End Sub

I've tried with this

Private Sub ComboBox1_Change()
[B]Workbooks("Userform.xlsm").Activate[/B]
[B]Worksheets("Order").Select[/B]
Workbooks("Userform.xlsm").Sheets("Order").Range("b2") = ComboBox1.Value
Workbooks("Userform.xlsm").Sheets("Order").Calculate
On Error GoTo row3
Me.ComboBox3.RowSource = Workbooks("Userform.xlsm").Sheets("Order").Range("D4").Value
row3:
On Error GoTo row4
Me.ComboBox4.RowSource = Workbooks("Userform.xlsm").Sheets("Order").Range("D5").Value
row4:
TextBox3 = Workbooks("Userform.xlsm").Sheets("Order").Range("b14").Value
End Sub

The error I mostly get is out of the range when it tries to deal with a cell in any way.

All of that works fine if workbook stays active in the background or if it is the only open workbook in the application
As soon as other workbook is activated, userform breaks as it can't get the source for it's objects
Any way to avoid and bypass this?
Thank you

 

[Fluff]

Try it like

Private Sub ComboBox1_Change()
With Workbooks("Userform.xlsm").Worksheets("Order")
    .Range("b2") = ComboBox1.Value
    .Calculate
    On Error GoTo row3
    Me.ComboBox3.RowSource = .Range("D4").Value
row3:
    On Error GoTo row4
    Me.ComboBox4.RowSource = .Range("D5").Value
row4:
    TextBox3 = .Range("b14").Value
End With
End Sub

 

[dado6612]

Hey Fluff, thanks for the effort
Sadly it gives an Error 380: Could not set the RowSource property. Invalid Property value
at line 9 Me.ComboBox4.RowSource = .Range("D5").Value
value is _23 with named range of a few rows
which is weird, i use the same cell reference for data validation list with indirect function, and there it works just fine
I think all of my problems across all the codes are related to rowsources not being set
Edit:
that named range to which rowsource tries to refer is not on "Order" sheet, but the scope of named range is set to the workbook
sheet name is "List"
could that be a problem? rowsource cant find it?

 

[Fluff]

Did it work to begin with?

 

[dado6612]

Did it work to begin with?

Works only if that is the only open workbook, not if any other workbook along the Userform is open.

 

[Fluff]

In that case it should still work as it's looking at the order sheet regardless of what sheet is active.
That said I've never used RowSource, but suspect it needs an address, rather than a value

 

[dado6612]

In that case it should still work as it's looking at the order sheet regardless of what sheet is active.
That said I've never used RowSource, but suspect it needs an address, rather than a value

Would you have any other idea of how to approach this problem then?
Basically few combobox selections define list for other comboboxes, as indirect validation list does, but it works only if one workbook is open
values in d4 and d5 are names for named ranges to look up, gotten from a formula.
I've tried so many options, with string variables for a rowsource etc..
No idea..

 

[Fluff]

Are the named ranges in the UserForm.xlsm workbook?

 

[dado6612]

Are the named ranges in the UserForm.xlsm workbook?

Yes, they all are part of the same workbook.

 

[dado6612]

Something like this did the trick for first 4 comboboxes.
Will play more to fix them all the same way and see how it goes

Private Sub ComboBox1_Change()
Dim what, that As Range
With Workbooks("Userform.xlsm").Worksheets("Order")
    .Range("b2") = ComboBox1.Value
    .Calculate
    TextBox3 = .Range("b14").Value
End With
If Me.ComboBox2 = "" Then GoTo capac
Me.ComboBox3.RowSource = ""
Me.ComboBox4.RowSource = ""
With Workbooks("Userform.xlsm").Worksheets("Lists")
If Me.ComboBox1.Value = "2" And Me.ComboBox2.Value = "Day" Then
Set what = .Range("f3:f4")
ElseIf Me.ComboBox1.Value = "2" And Me.ComboBox2.Value = "Night" Then
Set what = .Range("g3:g6")
ElseIf Me.ComboBox1.Value = "3" And Me.ComboBox2.Value = "Day" Then
Set what = .Range("h3:h6")
ElseIf Me.ComboBox1.Value = "3" And Me.ComboBox2.Value = "Night" Then
Set what = .Range("i3:i6")
End If
End With
ActiveWorkbook.Names.Add _
            name:="fill", _
            RefersTo:=what
Me.ComboBox3.RowSource = "fill"
capac:
With Workbooks("Userform.xlsm").Worksheets("Lists")
If Me.ComboBox1.Value = "2" Then
Set that = .Range("m7:m14")
Else
Set that = .Range("n7:n14")
End If
End With
ActiveWorkbook.Names.Add _
            name:="cap", _
            RefersTo:=that
Me.ComboBox4.RowSource = "cap"
End Sub

Im sure there is probably nicer and quicker way to do that.


For some weird reason, in this code, Label10 is not handled at all. Combobox8 gets hidden, but label stays unaffected, why is that?

If Me.ComboBox2 = "Day" Then
Me.ComboBox8.Visible = False And Me.ComboBox8.Value = "" And Me.Label10.Caption = ""
Else
Me.ComboBox8.Visible = True And Me.Label10.Caption = "Sun"
End If

I've tried with both label10.visible = false and label10.caption = "" ; nothing works
too many and arguments?
Thanks