VB programming interactive macroes

[Nils Morten]

I'm programming a little in Visual Basic for Excel 2002
My problem is how to do a interactive VB-macro that open
at specifikk folder, wait for the user to pick the right
file - and then continue the macro whwen this file is ben
chosed.
Can anyone tell me - or give me a sample to use?

 

[Norman Jones]

Hi Nils,

Try:

Sub MyTest()
Dim WB As Workbook
Dim fName As Variant

fName = Application.GetOpenFilename()
If Not fName = False Then
Set WB = Workbooks.Open(fName)
Else
'User cancelled
End If
End Sub

 

[Nils Morten Blakstad]

Thanks a lot mr Normann!
It realy works! )

I have still 2 questions, which I hope you can give me answers to (if I
may ask..).

Question 1:
How du I cose a specific folder in that macro?

Question 2:
How do I (in macroes) change the name of a specific sheet in my
woorkbokk (the name and the sheet will be variable)?

 

[Norman Jones]

Hi Nils,

For your first question, try:

Sub MyTest()
Dim WB As Workbook
Dim fName As Variant

ChDir "C:\Documents and Settings\NormanJones" '<<< CHANGE

fName = Application.GetOpenFilename()
If Not fName = False Then
Set WB = Workbooks.Open(fName)
Else
'User cancelled
End If
End Sub


Change the directory path to suit.

I am not sure that I understand your second question, but, perhaps,
something like:

Sheets(1).Name = "New Name"

 

[Nils Morten Blakstad]

Thanks again - very much thanks!!!!!
You realy is good at this!
I tried your solution for Q1 - and succeded again )

My problem No. 2 whas this:

How can I copy a text from a certain cell i the spreadsheet and paste
this text as name of the sheet - or e.g. a new sheet in the workbook?
The text in the cell will vary.

Is this a better description of the problem?

Regards Nils Morten
(I hope I don't bother you too much in this case!)

 

[Norman Jones]

Hi Nils,

Try:

Worksheets("Sheet2").Name = _
Worksheets("Sheet1").Range("A1").Value

Change the worksheet names and cell reference to suit.

 

[Nils Morten Blakstad]

Thanks again
I - by Your help - is getting closer to the solution.
But sadly I still dont get it right...
The macro command is sucsessfull when inserting spreadsheet no 2, but:
When inserting the 3rd,4th (and so on...)spreadsheet in the workbook,
the macro will not function (it referes to "sheet2" all the way).

Can I still hope to be uplightend of Your kindly competence once more?



Nils Morten Blakstad

 

[Norman Jones]

Hi Nils,

If I understand yourr latest post correctly, you want to rename each
worksheet in the workbook and the new name is in cell A1 of each sheet.

If this corresponds to your requirements, try:

Sub Tester()

Dim WS As Worksheet

For Each WS In ActiveWorkbook.Worksheets
WS.Name = WS.Range("A1").Value
Next WS

End Sub

Change A1 to the cell containing the worksheet name.