ComboBox: ListFillRange, Active WorkBook: CloseButton

[Stromma]

Hi All:

I use this to set the ListFillRange for ComboBox10:

With ComboBox10
.List = Sheets("Artikel").Range("A2:A5002").Value
.ListIndex = -1
End With

I want to use the same ListFillRange for ComboBox13, 18, 23, 27 and 32.

Do i have to set the ListFillRange for each ComboBox separately?

To prevent users from using the close button (x) in the UserForm i use:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
Cancel = True
MsgBox "InActivated!", vbCritical, "Microsoft Excel"
End If
End Sub

Is it possible use something similar to force users to close the workbook with a CommandButton?

Ex:
Sub Close()
ActiveWorkBook.Save
Application.Quit
End Sub

/Roger

 

[HalfAce]

Re: ComboBox: ListFillRange, Active WorkBook: CloseButt

Hi Roger,
To force the user to close with a button, what I have used is this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Worksheets("Sheet1").Range("A1").Value <> "Close" Then
Cancel = True
MsgBox " Please use the Exit button to close."
End If
End Sub

And then of course you need to have your command button enter "Close" into A1 of sheet1 at the beginning if its code.

This help?
Dan

 

[HalfAce]

Re: ComboBox: ListFillRange, Active WorkBook: CloseButt

Oh yeah,
It dawned on me that you'll NOT want to make A1 say "Close" at the beginning of your code. You'll want to have it enter that after you save the workbook. (Otherwise this will only work for you the first time.)
Sorry,
Dan