0

I have two commands of Application.CommandBars("Workbook Tabs").ShowPopup in my code. The first code works fine but the second code does not. The first command and the second command are doing the same thing, just showing workbook tab popup of a different worksheet.

I've added a 10 second pause between the first command and second command. I've also put the second command into a different module, calling it from the first module. I've also separated the first command in one sub and the second command into a different sub.

Sub Test()

Dim wbkA As String
Dim wbkB As String

Workbooks("Test.xlsm").Activate

wbkA = Range("B8")
wbkB = Range("B7")

Workbooks(wbkA).Activate

Application.CommandBars("Workbook Tabs").ShowPopup
Set ws1 = ActiveSheet
ActiveSheet.Move Before:=ActiveWorkbook.Sheets(1)

Workbooks(wbkB).Activate

Application.CommandBars("Workbook Tabs").ShowPopup
Set ws2 = ActiveSheet
ActiveSheet.Move Before:=ActiveWorkbook.Sheets(1)

End Sub

If you run the code one by one by pressing F8 in the debugger mode, it succeeds.

I expect not to get the debugger window and the code should run smoothly.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Korosuke
  • 41
  • 5
  • 2
    What exactly are you trying to achieve? Why so many `.Activate/Activesheet`? You may want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Siddharth Rout Sep 17 '19 at 01:51
  • Siddharth, Thanks for the comment. There is more to it in my code but I just took an excerpt from it because quite long. All I want to do is open two different Excel workbooks, select the appropriate Excel sheets within those workbooks and compare the data between the two. – Korosuke Sep 17 '19 at 04:07
  • For this then you do not need to select the sheets. or even use `Application.CommandBars`. Simply work with the objects directly. For example `Set wsA = WbkA.Sheets("SomeSheet")` and then `Set wsB = WbkB.Sheets("SomeSheet")` and then directly work with `wsA` and `wsB`... – Siddharth Rout Sep 17 '19 at 05:35
  • Thank you very much. The reason why I'm selecting application.commandbars is because there are multiple sheets in one workbook and users need to select the correct worksheet to make the comparison. They also need to select the correct workbook before too. To summarize, you need two user intervention for this workflow to work. If there's other ways, i'm all ears. Does your method work in this case? Sorry, I'm quite new to VBA so don't know too much.... – Korosuke Sep 17 '19 at 07:06
  • Yes there is a better way. Work with objects. Also try and open and close the relevant workbooks via code. If they are already open then use `Set WbkA = WorkBooks("Test.xlsm")` This way they do not need to select/activate them. Next Loop through the worksheets and offer the names in a combobox in a userform so that they can select the sheet names without actually selecting the sheets :) – Siddharth Rout Sep 17 '19 at 07:26

0 Answers0