VBA Userform combobox rowsource and folder value help

[ds16]

I have tried looking around here for the answer to my problems but no luck.

I am looking to do two different things....

Firstly, I am simply trying to populate a combobox based on a range in a specified workbook at a different location (I don't want the workbook to have to be open) for example I need the ranges of B5:B41,D5:D40 and F5:F45 of the workbook WC.xls located at C:/WC.xls all compiled into this combobox.


Secondly I am looking for my userform to automatically populate the next number that would come along in a series of file names at a specified location (for example, a folder full of files named ABC1, ABC2, ABC3, ABC4, and ABC5, I would need for the code to produce the number 6 (since that is the next number I would need the file name for)).


Any help or direction would be greatly appreciated!

---------- Post added at 09:46 AM ---------- Previous post was at 08:14 AM ----------

and also protect cells but only allow the userform to enter data?

---------- Post added at 12:26 PM ---------- Previous post was at 10:46 AM ----------

anything will help...

[royUK]

To populate form another workboo the simplest method is probably to open the workbook, grab the data then close. Hide this action by switching off ScreenUpdating

Your code would need to couunt the number of files in the folder, assumung they are all named like this.

Protect with UserInterface is the best way to allow the userform to interact with the worksheet

[ds16]

thanks for the link

any chance you have what the code would be like for the first two things? Still new to VBA so translating what I want to do into the syntax to do it is hard for me.

---------- Post added at 02:59 PM ---------- Previous post was at 01:24 PM ----------

got the protect cells down

[royUK]

Something like this

Please Login or Register  to view this content.

[ds16]

that looks good but when I try to run it it gets a run time error 438 (object doesn't support this property or method)

I changed the destinations to the right places let me know if I did something wrong...(MASTER should be changed to sheet name?)

Please Login or Register  to view this content.

that has end sub and initialize sub in it as well...

also do you have any code for the file number issue I have?

thanks for the help!

[ds16]

up for more suggestions

[royUK]

Where is the code erroring?

Try

Please Login or Register  to view this content.

[ds16]

Roy,

here is my updated code

Please Login or Register  to view this content.

the first line that causes an error when using step into is

Please Login or Register  to view this content.

[ds16]

also any help on the number generating from a folder full of files would be greatly appreciated...that is probably more important than populating the combobox

[ds16]

up for exposure

[ds16]

heres my workbook if anyone is interested

[ds16]

solved the file search problem along with some great help

here is my code if anyone is curious

Please Login or Register  to view this content.

if anyone is interested in what that did it searched the NCR folder for file names with NCR#### and returned the #### (and added 1) into my textbox1

[ds16]

up for more suggestions