switching to already open file in vba

[cocopops2001]

hey there, i have a workbook which is a sort of MENU/Database of various calculation sheets and for the most part it works fine.

what the book does is allows the user to select which calculation sheet they want and then click a button which asks the user what they want done with the sheet.

they have the option of creating a new book (works fine with no problems), opening an existing(closed) book(works fine too) or using a book that is already open(doesnt work so well).

these options are called fron a userform which has a dropdown box for the open files and a few buttons for the other functions. how do i switch to the open work book?

below is the code for the OK button which they push after selecting the open workbook

Private Sub CommandButton1_Click()
    
    Dim Myfile As String
        
    Myfile = ComboBox1.Value
    
    '''opens new book
    Myfile.Sheets(1).Activate
    
    '''''checks if cover sheet exists and if not creates one
    covercheck
    
    ''''copies desired sheet
    ThisWorkbook.ActiveSheet.Range("a11:n70").Copy
    
    '''''copies desired sheet
    sheetcopy
     
    UserForm3.Hide
    
End Sub

it worked fine when the OK button just showed a msgbox but now i want it to do somethin it becomes unstuck. i have also tried

workbooks.open myfile

which gives me the message about opening an already open file and that i might lose my changes.
have tried

workbooks.activate myfile

and

workbooks.select myfile

and

myfile.sheet(1).activate

the sheet in the new book does not matter, as long it is selected and becomes the active book the rest of the macro works.

please help

 

[wigi]

The syntax is different. For instance:

workbooks(myfile).activate

 

[cocopops2001]

ok got it fixed using

windows(myfile).activate

new to VBA but my hint to all newbies is use the MACRO recorder and then modify to suit.

never thought i would praise microsoft for much but the macro recorder has been one of their better ideas :D

 

[cocopops2001]

thanks for the reply, must of fixed it just as you posted

 

[wigi]

never thought i would praise microsoft for much but the macro recorder has been one of their better ideas :D

The macro recorder should be used to show you the method or property you want to have, not to write or generate code... You WRITE VBA-code, you do not RECORD it.

 

[wigi]

Why do you use Windows() instead of Workbooks()? Indeed, the macro recorder.

 

[cocopops2001]

no reason for windows instead of workbooks, i was getting stuck and that is what the recorder gave me when i selected manually. changed it to workbooks now though.

i am quite new to VBA so still not sure what command does what etc

 

[wigi]

no reason for windows instead of workbooks, i was getting stuck and that is what the recorder gave me when i selected manually. changed it to workbooks now though.

i am quite new to VBA so still not sure what command does what etc

No worries, everyone here started hesitating Just ask more questions if you need to / want to.

 

[Norie]

It's generally never needed to activate any workbook/worksheet/range etc to work with it.

If you have code that will only work properly when a particular workbook is active then it's probably worth taking a look at it.

It can probably be rejigged so it doesn't rely on which workbook is active to do what it's meant to.

It shouldn't be too hard to do that and might save a lot of trouble.

 

[cocopops2001]

i could probably get the code to work without the destination workbook being open but i prefer it to be active so that the user can see that the copying process has happened. stops clicking and clicking and ending up with more sheets than they need/want