keeping a userform visible

[Brian Matlack]

Hi!
Using <UserForm1.Show vbModeless> on my ms calendar 11.0 keeps it visible while selecting a new cell. Why doesn't it work for this?

<VBA.UserForms.Add(TempForm.Name).Show vbModeless>

I am using code that auto-builds a userform (and then deletes it) based on a list of headings (in the spreadsheet) for the option buttons. I'd show the code but its awfully long.

Any help would be appreciated and thanks for your time!!

[Tom Ogilvy]

If you are using John Walkenbach's code or based your code on hise, I used
this approach to get it to work:

Dim sTempFormName as String

' set the ShowModal propery to False
TempForm.Properties("ShowModal") = False
' Show the form
' VBA.UserForms.Add(TempForm.Name).Show
sTempFormName = TempForm.Name
Names.Add Name:="FormName", RefersTo:="=""" & sTempFormName & """"
Application.OnTime Now, "ShowForm"

' removed the code to delete the tempform.

then created a routine named ShowForm to actually show the form


Sub ShowForm()
Dim sTempFormName as String
sTempFormName = Evaluate(ThisWorkbook.Names( _
"FormName").RefersTo)
VBA.UserForms.Add(sTempFormName).Show
End Sub


That worked for me.

Here is a link to Walkenbach's code:
http://www.j-walk.com/ss/excel/tips/tip76.htm

Using a Public variable didn't seem to work - that is why I created a
defined name.

--
Regards,
Tom Ogilvy

"Brian Matlack" wrote:

>
> Hi!
> Using <UserForm1.Show vbModeless> on my ms calendar 11.0 keeps it
> visible while selecting a new cell. Why doesn't it work for this?
>
> <VBA.UserForms.Add(TempForm.Name).Show vbModeless>
>
> I am using code that auto-builds a userform (and then deletes it) based
> on a list of headings (in the spreadsheet) for the option buttons. I'd
> show the code but its awfully long.
>
> Any help would be appreciated and thanks for your time!!
>
>
> --
> Brian Matlack
> ------------------------------------------------------------------------
> Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
> View this thread: http://www.excelforum.com/showthread...hreadid=562904
>
>

[Brian Matlack]

Hi Tom! Thanks for your response!!
Yes I am using John W.s code I should have said so in the beginning. Sorry!

I added your code as indicated and it almost works. The form does remain visible now but when I first launch, it shows the form and puts the default selected item into the active cell automatically. I can select another cell while the form remains visable but when I click OK to insert the chosen item the form closes and nothing is put into the cell.

I'll keep working at it!
Any further help would be greatly appreciated and thanks again!!