MsgBox with no buttons

[cicconmr]

Is there any way to make a message button that a user can not click out of?

I want to use the message box as a way to inform the user that their list is being populated, then have the macro close the msg box after the important part of the macro is finished.

Any ideas?

Matt

 

[Trevor G]

I think you would need a Userform to achieve this.

 

[cicconmr]

That's what I was fearing....thos things are so annoying when you have more than one screen. It pops up all over the place.

 

[Trevor G]

OK if you don't like that, what about using a shape that is invisible whilst not running and becomes visible during a process.

General code is, so you can position it and colour it add text and then via hide via code and activate once you run a subroutine,

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> hideshape()<br><SPAN style="color:#00007F">With</SPAN> ActiveSheet.Shapes("rectangle 1")<br>.Visible = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Norie]

How would a userform pop-up all over the screen?

All you need to do is show it run the code and then close it.

You could even add some sort of status indicator to inform the user of progress.

 

[Peter_SSs]

In a similar vein, you could try something like this. Of course there would be no need to create/delete the worksheet each time if you don't want. You could have this sheet as part of your workbook with the message already populated then just have your code swith to this sheet while it does its other stuff then switch back to the one it came from at the end.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ListPopulating()<br>    <SPAN style="color:#00007F">Dim</SPAN> ActSh <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> ActSh = ActiveSheet<br>    Sheets.Add Before:=Sheets(1)<br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Range("E5")<br>        .Font.Size = 36<br>        .Value = "Your list is being populated"<br>        .Interior.ColorIndex = 35<br>        .EntireColumn.AutoFit<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#007F00">'Do your other stuff here</SPAN><br>    <br>    ActSh.Activate<br>    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>    Sheets(1).Delete<br>    Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[cicconmr]

I may be wrong about this but:

1) THe userform does pop up in random places when you have 3+ screens
2) Would not the user have to interact with the userform upon showing it before moving to the rest of the code?

 

[Peter_SSs]

2) Would not the user have to interact with the userform upon showing it before moving to the rest of the code?

No

 

[Norie]

1 You should be able to set the startup position of the form so it shows you where you want.

2 What I was thinking is that you call the code for the list from the userform's Initialize event.

Then when that code finishes close the userform, again from the initialize event.

 

[kpark91]

2) Would not the user have to interact with the userform upon showing it before moving to the rest of the code?

Me.Show (vbModeless)

Allows you to select and perform tasks in a workbook without interacting with the userform...

This reminds me, is there any other way to approach this? because the userform will just be deselected but not hidden.