show userform in excel addin

excel 2003 vba  excel addin

I have a worksheet that is created and loaded with data in any workbook that initiates the add-in..and requests the userform..

I'm running some code to load a userform...the code on the userform seems to be running, but the form is not showing up ?

The xla is called  iData

in a function in Add-in calling userform:

frmList.Show vbModal

userform code:

Private Sub UserForm_Activate()
Application.ScreenUpdating = True
With ListBox1
  .Selected(0) = True
End With
End Sub

Private Sub UserForm_Initialize()
  'used to load the data first
    Call LoadData
End Sub

Sub DetermineUsedRange(ByRef theRng As Range)
Dim FirstRow As Integer, FirstCol As Integer, _
   lastRow As Integer, LastCol As Integer
On Error GoTo handleError
FirstRow = Cells.Find(What:="*", _
     SearchDirection:=xlNext, _
FirstCol = 1
lastRow = Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
LastCol = Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
Set theRng = Range(Cells(FirstRow, FirstCol), _
   Cells(lastRow, LastCol))
End Sub

Public Sub LoadData()
On Error Resume Next
      Dim usedRng As Range
 ' get the rnge from the spreadsheet
         DetermineUsedRange usedRng
           ' select the range for the listbox ON USERFORM ADDIN
            ListBox1.RowSource = "List!" & usedRng.Address

End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Have you stepped through the code? Are you sure nothing else is unloading the form again? (presumably you have some event code in the form?)
Chris BottomleySoftware Quality Lead EngineerCommented:
Is the excel application visible and/or are you viewing the same instance of the excel as that where the form is opened?

i.e. is being exerted in the excel instance calling add-in rather than a different instance of excel that is open and active at the time.

FordraidersAuthor Commented:
NO OTHER CODE. is unloading form..all code posted thus far is from the userform...
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

FordraidersAuthor Commented:
i.e. is being exerted in the excel instance calling add-in rather than a different instance of excel that is open and active at the time.

if your asking me, is the workbook using using the add-in calling the userform---- No it is not...not from any command button or toolbar...
I'm calling the useform to open from a module in the Add-in itself
A module in add-in(iData)  is calling the "frmlist" useform to open...
Rory ArchibaldCommented:
How is the addin code being called, and how are you determining that the form code is running?
FordraidersAuthor Commented:
here is vbe
FordraidersAuthor Commented:
The add-in is loaded

The add-in is creating a toolbar menu.
The menu is pressed asking for a range via user input"A2"

AFTER A VALUE IS ENTERED in the worksheet in A2...
THE KEY COMBINATION being captured by a keyhook sub..

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal target As Range)

If xlApp.ActiveWorkbook.Name = wName$ And xlApp.ActiveSheet.Name = wNameSheet$ Then

        On Error Resume Next
        If target.Column = "" Then
            If target.Column = mRangeColumn Then
            End If
        End If
    End If

End Sub

Public Sub KeyHook()
        xlApp.OnKey "+~", "mfSearchResult"
End Sub
Public Sub KeyUnhook()
    xlApp.OnKey "+~"
End Sub

if it is successful it fires off a macro called  "mfSearchResult"
Which creates worksheet in the actual excel workbook...

the worksheet is loaded with data via access..

I'm trying to open the userform and trying to load a listbox on the userform from sheet created.

for reference:


FordraidersAuthor Commented:
tried this too !
Sub ShowUI()
    Dim frm As frmList
    Set frm = New frmList
    Load frm
End Sub
zorvek (Kevin Jones)ConsultantCommented:

You are going to have to do some debugging to figure out what is going on. Put some Debug.Print or MsgBox statements in the form code to determine if the code is even running and, if so, how far it is getting before the form disappears.

If the form never appears then try setting the startup position to center screen.

Rory ArchibaldCommented:
You wouldn't use load frm there, FYI.

Anyway, that doesn't really answer my question: how do you know the form code is running, and where do you actually call the form?
I assume you are not using shift enter to enter data because your code won't run in edit mode.
FordraidersAuthor Commented:
in module 4
Public Function GetDataFinal(rng As Range) As String

code loading data to worksheet

 ShowUI     <============== show me a form holding the data containing more than one record.

end function

I have stepped through the code, and  the code in the initiate form event of userform fires off and

in the original post above:
' select the range for the listbox ON USERFORM ADDIN
            ListBox1.RowSource = "List!" & usedRng.Address   <----- do you have to explicitly reference the form the objects are at ?

zorvek (Kevin Jones)ConsultantCommented:
You would get an error, I believe, if the RowSource property is set to an invalid reference.

Is the form ever visible? Did you check the form position settings? Try removing parts of the code until the form DOES display and note what is taken out just before.

FordraidersAuthor Commented:
no the form is never visible...

if I call the form before any code gets runnning..The form will show up ??
zorvek (Kevin Jones)ConsultantCommented:
Then start removing code until the form behaves as desired. Note what was removed just prior to it starting to appear. You need to narrow it down by trying different scenarios. We are unable to see anything unusual in your code.

Rory ArchibaldCommented:
One thing is that if you can carry on working, your firm is being unloaded or hidden (since you show it modally). You might add code to the terminate and deactivate events to pop up a message.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:

once I got rid of that in the debugging...the form displayed... !

rorya, for stciking with the problem...Appreciate it very much
FordraidersAuthor Commented:
rorya, for stciking with the problem...Appreciate it very much
FordraidersAuthor Commented:
sorry, I meant..

  Application.ScreenUpdating = False  was causing the problems
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.