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.

 

[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!

After I added your code I tryed to launch the code and got a run-tim
error '424': "Object required" when I debugged th
<TempForm.Properties("ShowModal") = False> code was yellow hi-lited.

I put your Dim code at the top with the rest of them
I put <TempForm.Properties("ShowModal") = False> directly under that
I put the
sTempFormName = TempForm.Name
Names.Add Name:="FormName", RefersTo:="=""" & sTempFormName & """"
Application.OnTime Now, "ShowForm"
directly under the <VBA.UserForms.Add(TempForm.Name).Show> however th
first two lines are already there (at the top of the code after Dims)

I removed the code that deleted the tempform but I am unsure what to d
with the showform subroutine code you gave me since I use the followin
code to launch the whole thing

Dim Ops() As Variant
Dim Cnt As Integer, i As Integer
Dim UserChoice As Variant
If Not Intersect(ActiveCell, Range("vendors")) Is Nothing Then
Cnt = Range("VENDORLIST").Count
ReDim Ops(1 To Cnt)
For i = 1 To Cnt
Ops(i) = Range("VENDORLIST").Range("A1").Offset(i - 1, 0)
Next i
UserChoice = GetOption(Ops, 1, "Vendors")
If UserChoice = False Then
ActiveCell.Value = ""
Else
ActiveCell.Value = Ops(UserChoice)
End If
End If
End Sub

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