keeping a userform visible

  • Thread starter Thread starter Brian Matlack
  • Start date Start date
B

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!!
 
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.
 
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!
 
Back
Top