VBA: Open Document based on Cell Value with multiple conditions

Question

I'm still new to VBA so I'm looking for advise to accomplish the following task: I Have a cell A1 and two Checkboxes (Serverbox1 & Localbox2)

A1 is a Pulldown menu with values like Apple1, Banana1 etc... which represent excel file names. Excel should open the picked file and exporting a cell or a range, for example A2 from WB1 to WB2 by clicking the Export Button.

Private Sub exportData_Click()

Dim WB1 as Workbook

Dim WB2 as Workbook

Server="C:\Server\Apple1.xls"

Local="C:\Local\Apple1.xls"

Set WS1=ThisWorkBook

Set WB1=ActiveWorkbook

WS2=WB2.Sheets(1)

Set WB2=Workbooks.Open(Server OR Local) Somehow that needs to changes automatically.

So that Set WB2=Workbooks.Open(Server)only applies If A1 = Apple1 & Serverbox.value = true & Localbox2.value= false

And Set WB2=Workbooks.Open(Local) only applies If A1 = Apple1 & Serverbox.value = false & Localbox2.value= true

Once the file is open it would continue to copy data to Apple1.xls or Banana1.xls based on the input above.

ws2.range("A2")=ws1.range("A2")

I dont need to worry about auto saving anything, it will be a manual process (preferred). I appreciate any help!

The file locations are mutually exclusive; radio buttons or a dropdown would have been a better idea than checkboxes. — dev1998

dev1998 dev1998 · Accepted Answer · 2016-11-23T20:01:10

Here are some ideas you can use. You'll have to update your code in a couple of places.

Server="C:\Server\" & A1 & ".xls" 'Get the filename from Cell A1

Local="C:\Local\" & A1 & ".xls"   'Get the filename from Cell A1


If (Serverbox.value = true & Localbox2.value= false) Then
    WB2=Workbooks.Open(Server) 
End If

If (Serverbox.value = false & Localbox2.value= true) Then
    WB2=Workbooks.Open(Local) 
End If

VBA: Open Document based on Cell Value with multiple conditions

1 Answers