File Path in UserForm connected to macros

Hello All,

First of all, I'm happy that my Excel knowledge is developing thanks to you. I'm using my macros now for months and they work fine. Now, I'm trying to improve them, But I came up with an idea to increase dynamism, and got stuck. The main idea is that more users use my macros, but everyone has a different path in the files for them. For this reason, I would like to have my UserForm a little extra like this.

Attachment 268600

Here, People could put in the path name of their file. And heres a little twist. The macros would use these when opening a workbook. I think is more User Friendly then changing the code every time I send them an update of the macro.

So, basically, a user could edit that text enter the file pathname like "C:/Users/temp.xlsx", then save it.The pathname would be saved upon pushing the command button to save and stored as a string. I would use these variants for my other macros, f.e., when opening a file:

Workbooks.Open Filename:= _
"C:/Users/temp.xlsx" (which here would be 'path1')
Sheets("Sheet1").Select


My questions would be:

- Is this all possible, if I use a Textbox, can I enter data in it? I didnt find any Inputbox in Toolbar.
- How should I define the saved variant, and how can other macros access it?
- How can I make the save command button to update the variant and text out the latest save in the path display?

I know it is much, hope you can help me! This would greatly help me understand the cross-references in Excel.

Thank you in advance,

Ambrus

@Solus

Thanks, I looked into it, and got it to work so far, that i get the browser popup choose an xlsx file and then a msgBox displays the filepath. So far so good. I will simply put this into the CommandButton1 onclick section, So it will be a lookup button basically. Coool. I got it stored now.

Sub filepath()

x = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls*", Title:="Select", MultiSelect:=False)

MsgBox "Path: " & x

End Sub

I also figured out the initilaze settings.

Private Sub UserForm_Initialize()
With Me
.TextBox1.Value = "C:/whatever.xls" ' End With

End Sub

I also arranged a choose file button.


So now the next level, what i dont understand. Save button -> updating the path. So when I push the save button, then a currently written value in the text box will update it. But again, If I open the whole thing again, Initialize will rewrite the path.

I read about global variants. But I would need something that i can give a value and it stores it, meaning if I quit Excel and open again, it will stay the same, until I change it with the save button.

Can I get a variant into here, which is feasible for that?

Example:
So, when I open excel and the user form. the given txtbox1 value is because of initialize : C:/whatever.xls. I would click on a search button (...), then the selected file automatically is written into txtbox1. When I push the save button it rewrites Intitialize function to the new value.

Is this possible?

Hoping for the best,
Ambrus

Okies, I will arrange that, but need some time to get it together. The easiest way would be to do a video really :D Will reply later today or tomorrow. Thanks so far!

So. Sorry for answering this late, but the time I wanted it was fragmentation day, and then I didnt have time. I did some research, so I understand also what I want.

-> I would need an Excel workbook working as a database where I can store fix data and assign them to my macros, and if I want, then rewrite it.

I tried to get a working macro, that exctracts data from a closed workbook, but didnt manage to find one.

What I am thinking should work is the following:

I have an textbox in the userform. I have a browse button (...), and a save button, and a label. See the picture below as attachment.

filepath.jpg

If you start the macro, this userform shows up, and in the first row, a path value is shown, which I set preliminary in another workbook. Lets say this is „C:\temp”

So actually the initialize for the inputbox should bring a value from a closed workbooks cell.

When I push the search button, then I can choose a file, GetOpenFilename works fine here. I would assign this file to overwrite the initial value in the textbox to what i chose.

If I push save also, the new value would be written in the closed workbook at the refering cell, from which the original intialize value is from.

Next time, initialize will bring the newly saved value from the workbook.

I hope now its clear. I know this is not easy, but logically it should work.

What I am missing is: Reading and assigning values to closed workbooks. (Probably some more also) :D

Update. I did manage a helpful macro here. So extracting a value from a special cell while the workbook is closed works now:

Please Login or Register  to view this content.

Now all I am missing is how to add value to a specified cell in a closed workbook. Im pretty stuck. :/

Actually this works, but this opens and closes the workbook, not the best. :/

Any idea how to let it be closed?

Please Login or Register  to view this content.