I am relatively new with using VBA 1

[aharris2693]

I am relatively new with using VBA for Excel and I am trying to reference two workbooks at the same time. What I want is for the user to make changes to a weekly "move list" that will then update another sheet that is a seating chart at the end of each week. Based on the names in the move list, I want to search the seating chart and then make the appropriate changes. I tried recording a macro that opens the new workbook and makes a simple change just to see the code for using 2 books. This worked and it still worked when I adjusted for my search variables. My question, finally, is why doesn't this same code work when I put it under the click event of a command button. Below is the code, but like I said it works as a macro, so it may not be of any assistance.

Sub updateSheets()
a = 2
Do Until a = 6
strName = Range("Move_List!A" & a)
strOffice = Range("Move_List!F" & a)
MsgBox (strName & ", " & strOffice)
ChDir "F:\DATA\USERS\HarrisAL\Mitch"
Workbooks.Open FileName:= _
"\\WIL-OPS\VOL1\DATA\USERS\HarrisAL\Mitch\Wilmington_Seating_Chart.xls"
x = 2
Do Until x = 1000
If strName <> &quot;&quot; Then
If Range(&quot;D&quot; & x) = strName Then
Range(&quot;B&quot; & x).Select
ActiveCell.Value = strOffice
End If
End If
x = x + 1
Loop
ActiveWorkbook.Save
ActiveWindow.Close
a = a + 1
Loop
End Sub


Thanks a lot for any help

 

[xlbo]

Bet you are using the &quot;controls toolbox&quot; command button. Simply change the TakeFocusOnClick property to false and it should work

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[aharris2693]

Thanks Geoff

The error is now gone, however it doesn't update the sheet, because it considers the active cell to be that cell in the sheet that the button is on, so it will never match up. Can I just call the macro when the button is clicked?
Thanks again for the help

 

[xlbo]

Instead of:
Range(&quot;B&quot; & x).Select
ActiveCell.Value = strOffice

use:
Range(&quot;B&quot; & x).Value = strOffice

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[aharris2693]

Thanks again Geoff
I am still getting the same value which is the 'D' column in the move list sheet rather than the sheet that I am trying to search.

 

[aharris2693]

Geoff
Everytime I get stuck using something, I find other ways to do the same thing and usually end up at the same place. I think what my question should be is if there is anywhere that explains the difference between a. macros, b. form objects, and c. control objects. Is there a FAQ, previous thread, or anything else that you know of that explains this. I have trouble understanding why bits of code work fine in one place and then produce errors when used in a different area. I really appreciate all of your help on this and other questions I have had.

 

[xlbo]

Don't think there is a FAQ and I can't think of any previous threads that go into detail. Your best bet is to look at the object model in excel (press F2 in the VBE and it will appear). You can look at the properties and methods of all the objects in there. In this instance, I think that the problem is that to reference a cell, when you have 2 workbooks open, you need to reference the correct workbook

eg msgbox workbooks(1).worksheets(1).range(&quot;A1&quot.value
msgbox workbooks(2).worksheets(1).range(&quot;A1&quot.value

try adding
dim tWB as workbook, oWB as workbook

1st line of code:
set tWB = thisworkbook

after
Workbooks.Open FileName:= _
&quot;\\WIL-OPS\VOL1\DATA\USERS\HarrisAL\Mitch\Wilmington_Seating_Chart.xls&quot;

set oWB = activeworkbook

Then use these when referring to ranges


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244