Need Help With Input Box To Open File in Specified Path

[ham123]

Greetings experts,

I am trying to create an input box which will request for a file path to put into a code before running the code. Attached here is an example file:
https://filedb.experts-exchange.com/incoming/2019/04_w14/1416803/example.xlsm

Right now, I have created an example file with a button. When this button is pressed, the inputbox should appear.
23984

After the user presses ok, the file path should be put in between the single apostrophes of this code:
23985

If they didn't input anything, they should prompted to re-enter.

After that, the form should appear. For subsequent uses, if there is already a file path saved, a different inputbox should appear asking if the saved file path is the correct file path which will also be able to change the file path if needed.

Any help is much appreciated :)

[大灰狼1976]

Hi ham123!
Please refer to the attachment.

[大灰狼1976]

A slight modification.

Sub AddCodeToThisWorkbook()
Dim i&, s$, s1$, pthTmp$
s1 = " Application.Run" & Chr(34) & "'" & pth & "'!ShowForm" & Chr(34)
With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
For i = 1 To .CountOfLines
s = .Lines(i, 1)
If s Like "*Application.Run*ShowForm""" Then
pthTmp = Split(s, "'")(1)
If pthTmp = "" Then
.ReplaceLine i, s1
Else
If MsgBox("Whether to replace the path?" & Chr(10) & pthTmp & Chr(10) & "to" & Chr(10) & pth, vbYesNo) = vbYes Then
.ReplaceLine i, s1
End If
End If
Exit For
End If
Next i
End With
ShowForm
End Sub

[ham123]

Hi, thanks for your reply! :) I managed to do it like this.

Sub OpenFile()Dim sFileName As Variant


sFileName = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
If sFileName <> False And Dir(sFileName, vbNormal) <> "" Then
Call ShowForm(sFileName)
End If
End Sub


Sub ShowForm(ByVal FilePath As String)
'Please add your error handlers
Application.Run "'" & FilePath & "'!ShowForm"
End Sub




However when I press cancel at this step:
23988

This appears:
23989

Can you help me add in an "on error" procedure? As I do not want the user to see the run time error

[大灰狼1976]

if there is already a file path saved, a different inputbox should appear asking if the saved file path is the correct file path which will also be able to change the file path if needed.
I did it according to your request. So I have to edit the code use vba.
It must be very simple if it's not needed.

[大灰狼1976]

I can't download you attachment at #4, but error handle is simple.
like below:

Sub ShowForm()
On Error GoTo ErrHandle
Application.Run "'" & FilePath & "'!ShowForm"
Exit Sub
ErrHandle:
MsgBox "Error!"
End Sub

[ham123]

I tried adding it in like this but it still showed it

Sub OpenFile()Dim sFileName As Variant


sFileName = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
If sFileName <> False And Dir(sFileName, vbNormal) <> "" Then
Call ShowForm(sFileName)
End If


On Error GoTo ErrHandle
Application.Run "'" & FilePath & "'!ShowForm"
Exit Sub
ErrHandle:
MsgBox "Error!"
End Sub


Sub ShowForm(ByVal FilePath As String)
'Please add your error handlers
Application.Run "'" & FilePath & "'!ShowForm"



End Sub




I have also tried it in the the Showform code as well

[大灰狼1976]

Sub OpenFile()
Dim sFileName As Variant

sFileName = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
If sFileName <> False And Dir(sFileName, vbNormal) <> "" Then
Call ShowForm(sFileName) 'Because error handle is not added here.
End If
End Sub

Sub ShowForm(ByVal FilePath As String)
On Error GoTo ErrHandle
Application.Run "'" & FilePath & "'!ShowForm"
Exit Sub
ErrHandle:
MsgBox "Error!"
End Sub

[大灰狼1976]

Please refer to the attachment.
You can change the code what you want. but error handle is ok.

[ham123]

Okay, thank you! can you give me an example of the file path?
I tried putting in my own file path but it didn't work..
For example: C:\Users\ESPZYONG\Documents\Projects\HM\HM01

[大灰狼1976]

Put these two files in the same path, then run the code.

[ham123]

Hi, I managed to figure out the file path but I get this error
23993

[大灰狼1976]

Trust Center Chooses Trust or change to your posted code.

[ham123]

Here is the file with the form
https://filedb.experts-exchange.com/incoming/2019/04_w14/1416830/HM01-Without-Password.xlsm

[ham123]

Now it says this.. Maybe you can take a look at the file I am trying to open? :)
23994

[大灰狼1976]

test my posted files at #11

[ham123]

It says the same error as in #12

[大灰狼1976]

I explained the solution about error #12 at #13.
I will post the picture later.

[大灰狼1976]

23995
like this

[ham123]

I think I will go for my original method and I will debug the on error procedure. I will post the final solution here later.

[大灰狼1976]

Sorry, The picture post failed in #19.
24000
you can also use you original code with error handle.

[大灰狼1976]

Is png format not uploadable? try again used jpg format.
24002

[MagPower]

Do you have an English version of the picture?

[大灰狼1976]

Sorry, I don't have it, But according to the content of the picture, should be able to find the corresponding options.

[大灰狼1976]

An example used your original code with error handle.
I'm disappointed that didn't use code editing.:(

[ham123]

Hi I managed to debug it


Sub OpenFile()Dim sFileName As Variant


sFileName = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
If sFileName <> False And sFileName <> "" Then


Call ShowForm(sFileName)


End If
End Sub


Sub ShowForm(ByVal FilePath As String)


Application.Run "'" & FilePath & "'!ShowForm"

On Error GoTo ErrHandle
Application.Run "'" & FilePath & "'!ShowForm"
Exit Sub
ErrHandle:
MsgBox "You canceled the process!"
End Sub

[大灰狼1976]

@ham
Are you sure "on error" under "application.run" is no problem?


--Okami