PDA

View Full Version : Solved: Force to go to page based on condition



austenr
08-20-2007, 07:48 AM
In the code below if the Solver add-in gets installed ok, I need to for the activation of a page called Class Notes, if not and there are errors installing it should go to a page called Installation Instructions.

Sub FindSolverexcel2003()
'Search the add-in file and try to install
On Error Resume Next
With Application.FileSearch
.NewSearch
.SearchSubFolders = True
.filename = "Solver.xla"
.LookIn = Application.Path
.Execute
If .Execute > 0 Then
If AddIns("Solver Add-in").Installed = False Then
AddIns("Solver Add-in").Installed = True
'If not installed check cause
If Err.Number > 0 Then
'The cause is a security restriction
If Err.Number = 1004 Then
MsgBox "Please check if your security setting do not allow you to use VBProject" _
& vbCr & "Click Tools, Macro, security" & vbCr _
& "trusted source check access to Visual Basic Project" & vbCr & "close and open the file again" _
& vbCr & "if there is no option then proceed"
Err.Clear
Exit Sub
Else
'The cause is it's just not in the add-in list yet and it try to reinstall again
AddIns.Add(.FoundFiles(1)).Installed = True
Err.Clear
End If
'Give message installation successful
'CreateObject("WScript.Shell").Popup "Solver successfully added!"
End If
End If

Else
MsgBox "Solver not installed on this conputer", vbCritical
Exit Sub
End If
End With

Dim i As Integer, x As Long
'Checks to see if solver is already a reference in the VBA tool
x = ThisWorkbook.VBProject.References.Count
For i = 1 To x
If ThisWorkbook.VBProject.References(i).Name = "SOLVER.xla" Then
CreateObject("WScript.Shell").Popup "Reference already set", 1, "Solver"
Exit Sub
End If
Next i
'This puts the solver as a reference in VBA tool
ThisWorkbook.VBProject.References.AddFromFile Application.LibraryPath _
& Application.PathSeparator & "SOLVER" & Application.PathSeparator & "SOLVER.XLA"
'CreateObject("WScript.Shell").Popup "Reference successfully installed", 1, "Solver"

'This just clears the solver bugs
Application.Run "Solver.xla!auto_open"
End Sub

rory
08-20-2007, 08:54 AM
Can't you just add a check at the end to see if Err.Number = 0 or not?
Incidentally, if the registry has not been secured, you can set the access to the VBProject using code like this:
Sub EnableVBAccess()
Dim wsh As Object, strKey As String
Set wsh = CreateObject("WScript.Shell")
'key to modify
strKey = "HKEY_LOCAL_MACHINE\Software\Microsoft\Office\" & _
Application.Version & "\Excel\Security\AccessVBOM"
'enable access
wsh.RegWrite strKey, 1, "REG_DWORD"
End Sub

austenr
08-20-2007, 09:22 AM
Where would you check for the error number = 0?

rory
08-20-2007, 09:28 AM
Simplest method I think would be to select the Installation Notes page at the start (as a default) and then if you get to the end of the routine and err.number = 0 then you know nothing went wrong so you can activate the Class Notes page? You might also want to activate it in the section where you check if the reference is already set.