VBA Userform with inactive workbook

dado6612

Well-known Member
Joined
Dec 4, 2009
Messages
579
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
Code:
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
Code:
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
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,436
Office Version
365
Platform
Windows
Try it like
Code:
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

Well-known Member
Joined
Dec 4, 2009
Messages
579
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?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,436
Office Version
365
Platform
Windows
Did it work to begin with?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,436
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Dec 4, 2009
Messages
579
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,436
Office Version
365
Platform
Windows
Are the named ranges in the UserForm.xlsm workbook?
 

dado6612

Well-known Member
Joined
Dec 4, 2009
Messages
579
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
Code:
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?
Code:
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
 
Last edited:

Forum statistics

Threads
1,078,285
Messages
5,339,296
Members
399,292
Latest member
Bdbd55

Some videos you may like

This Week's Hot Topics

Top