Pop msgbox but still allow excel to be available

[brulet]

Hi,

I've been trying to pop a message box with a button that computes a value, with a compute button, and isn't active, and allows the user to access the spreadsheet to enter some values. Is there a setting to show the msgbox, but make the spreadsheet active over the msgbox (I guess also making the msgbox always stay on the top).

Thanks for any help you guys can provide,

BT

 

[brulet]

Hi,

I've been trying to pop a message box with a button that computes a value, with a compute button, and isn't active, and allows the user to access the spreadsheet to enter some values. Is there a setting to show the msgbox, but make the spreadsheet active over the msgbox (I guess also making the msgbox always stay on the top).

Thanks for any help you guys can provide,

BT

Showmodal = false .. ahh :D

 

[Ruddles]

Not as far as I'm aware - MsgBox holds the focus until you dismiss it.

You could use a non-modal userform but as soon as you click on the worksheet, the userform will drop behind the worksheet.

You could use Application.InputBox to get the address of a cell and then do a .Value on that:-

Set MyRange = Application.InputBox("Select range:-", , , , , , , Type:=8)
SomeVariable = MyRange.Value

You'd be safest checking that the user had only selected a single cell before trying to do a .Value on it.

 

[Jaafar Tribak]

In case you are interested, sometime ago I coded some modeless MsgBox. Take a look at post6 HERE.

Obviously, the suggested modeless userform approach is much easier.

 

[Ruddles]

Excellent - well worth keeping a copy of that for the occasion when it comes in useful.

@Brulet: the code pasted into a pair of standard modules and workd first time, so it will only cost you a couple of minutes to test this.