Begin Dialog type mismatch error with Excel reference

  • 1
  • Question
  • Updated 4 months ago
Hello
I'm trying to prompt a user dialog with mutliple dropdown lists for which the arrays are stored inside an excel file (basically : open excel >> story arrays >> get user to select options in dialog).

I've managed to make user dialogs work in the past, but by adding the Microsoft Excel 16.0 Object Library (1.9) reference, I now have a "Type Mismatch" error when calling the dialog. 

I've also noticed that adding/removing the excel reference changes the color of the command Dialog in the macro editor (going teal to black), which makes me think that excel might be getting the priority over the command.



I've also noticed that the dialog pops up and disappears extremely quickly on screen before getting the error, which I have no idea why. I have recorded using OBS and watched frame by frame and can confirm that it is the user dialog specified.

- I have no other dialogs in my macro. 
- I'm using MindManager 19 / Office 365 / Windows 10 Pro.

Is there a way to circumvent this issue, or prompt user forms differently, such as by using excel userforms?

Thanks so much
Photo of Jeremy Bergeron

Jeremy Bergeron

  • 2 Posts
  • 0 Reply Likes

Posted 5 months ago

  • 1
Photo of Jeremy Bergeron

Jeremy Bergeron

  • 2 Posts
  • 0 Reply Likes
Hi so I might have misunderstood the issue. It seems that how I defined my array caused the type mismatch issue. 
I used :
Dim arr as Variant
arr = ws.Range("1:1").Value
But making the array differently, like :
    Dim arr(2) As String
    arr(1) = "A": arr(2) = "B"
Worked

I still don't know why the Dialog function changes color in the editor with the Excel reference enabled, nor why the type mismatch error appears on the Dialog dlg line, but at least I can make it work.
(Edited)