ComboBox: ListFillRange, Active WorkBook: CloseButton

Stromma

Board Regular
Joined
Feb 8, 2004
Messages
240
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Re: ComboBox: ListFillRange, Active WorkBook: CloseButt

Hi Roger,
To force the user to close with a button, what I have used is this:
Code:
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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, :oops:
Dan
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,093
Messages
5,768,047
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top