0

I currently have a form with a couple of buttons, text boxes and a AcroPDF. AcroPDF is an additional control I have added to my toolbox and I get it from "Adobe PDF Reader".

This macro is being used on various computers and I have found out that, for some reason, the macro does not work on all computers. But when I delete the AcroPDF control from the form, it works for all computers. For the computers that do get an error, it happens when I first open the file I automatically get an error msg and the form does not automatically show up (like how I programmed it to).

Is there a way I can program this into my "Thisworkbook.open" so that if there is an error, I can somehow delete or disable this additional control? something similar to how you are able to turn on and off references? (see below)

ThisWorkbook.VBProject.References.AddFromFile ("libraryName")

UPDATED: the error message I get from the computers that don't work is the following:

system error &H80004005 (-2147467259). unspecified error

Thanks.

NoobProgrammer
  • 181
  • 1
  • 1
  • 11
  • what's the error message when you open the file on the other computers? – David Zemens Nov 11 '16 at 19:23
  • @DavidZemens I have updated my question to add the error – NoobProgrammer Nov 11 '16 at 19:42
  • If you break and debug, what line is raising that error? – David Zemens Nov 11 '16 at 19:43
  • The entire spreadsheet just crashes right away. I have the form set to open automatically. so when I first open the spreadsheet I get the error, I get a second msg asking to debug or end so I click debug, and then I get a third msg saying the same error msg and then it crashes – NoobProgrammer Nov 11 '16 at 19:51
  • Put a breakpoint on the first line in the form's `Initialize` or `Activate` events, step through the code using F8 and try to identify what line is raising the error. – David Zemens Nov 11 '16 at 19:52
  • Are ActiveX controls enabled on the affected computers? http://stackoverflow.com/questions/27411399/microsoft-excel-activex-controls-disabled?noredirect=1&lq=1 – David Zemens Nov 11 '16 at 19:56
  • yes I do have activex controls, sorry I will get back to you David, currently figuring this out on other computer. will get back to you in a couple of mins. thank you very much – NoobProgrammer Nov 11 '16 at 20:02
  • @DavidZemens thanks for waiting david. so I tried entering break points but I just get the same errors on the very first line which is "form1.show vbmodeless". I do get a new error msg saying that I am out of memory when I am not. it says to switch to a 64 bit which all computers and excel versions are 64 bit, I just checked. activex controls are on, i will check out your link – NoobProgrammer Nov 11 '16 at 20:07
  • OK, I'm afraid there's not much I can do on this but if you google that error message, there are a lot of results that probably can point you in a better direction. – David Zemens Nov 11 '16 at 20:27
  • @DavidZemens i have looked at your link and i have tried deleting those files on the computers that do not work but still no luck. Its something with this AcroPDF control. – NoobProgrammer Nov 11 '16 at 20:27
  • @DavidZemens Thank you david for all your help. i will also try reversing the logic like you suggested if i can't figure it out – NoobProgrammer Nov 11 '16 at 20:28

2 Answers2

0

UserForm.Controls.Remove -- but this only works for controls added at runtime (dynamically). You cannot use the Remove method on a control that was added from the Designer.

Probably you need to reverse this logic, and only add the control when the reference exists on the user's machine.

Dim ctrl
If Len(Dir("libraryname")) <> 0 Then
    Set ctrl = UserForm1.Controls.Add ...

End If

You would also then want to use the Remove method when closing the form, that way the form remains in a state that could be opened on either computer.

HOWEVER, the error message you get on the other machines may shed additional light on the source of the problem. Depending on what the error is, there may be other solutions that don't involve removing the control.

David Zemens
  • 51,213
  • 11
  • 70
  • 118
  • Hi David, I have updated my question to show the error msg I get – NoobProgrammer Nov 11 '16 at 19:42
  • Hi David, I am going to try your answer above but I have a question. What "libraryname" am I looking for? I didn't add any references to my workbook, I only added the control and then I created it on my form. Maybe I am not understanding this correctly. Guide me oh wise one – NoobProgrammer Nov 16 '16 at 19:23
  • You need the reference path to the Acrobat PDF Reader library/dll. – David Zemens Nov 16 '16 at 19:37
  • Oh I see, thanks. I have been looking up some code and I was able to create a command button using Set ctrl = Me.Controls.Add(bstrProgID:="Forms.commandbutton.1", Name:="CommandButton1", Visible:=True) but I am not able to create a AcroPDF, is the name different? – NoobProgrammer Nov 16 '16 at 19:39
  • `"Forms.CommandButton.1"` is a MSForms.CommandButton object. The AcroPDF is something else. I don't have it, and can't verify what it should be called, but know that each *type* of control has its own string that you would pass to the `Me.Controls.Add` method. – David Zemens Nov 16 '16 at 19:42
0

If you don't mind creating a duplicate form without the AcroPDF control you might be able to use labels and the GoTo statement.

Sub Workbook_Open()
' do stuff
On Error GoTo AcroPDFError
ThisWorkbook.VBProject.References.AddFromFile ("libraryName")
' load your form with the AcroPDF control here.
Continue:
On Error GoTo 0

' the rest of your Workbook_Open sub is here
Exit Sub
AcroPDFError:
' load your form without the AcroPDF control here.
GoTo Continue
End Sub
Lucas Kellner
  • 361
  • 1
  • 7