Author Topic: Making VBA code work for duplicate (multiple instances) shapes  (Read 6551 times)

0 Members and 1 Guest are viewing this topic.

mmulvenna

  • Full Member
  • ***
  • Posts: 91
I have some VBA code that is executed from the actions section of the shape sheet by using the RUNMACRO function. The code adds some shapes to the page based on the postion of other shapes in the selected group of shapes. It then changes a number of shapesheet values for the shape(s) that were added by the code. Many of the shape sheet formulas reference other shapes in the selected group.

If I make a MASTER from my group of shapes and then add multiple instances of the master will the VBA code work across the instances?

Here is a small sample snipet of the VBA code
.....
.....
      strusercell = "room"
      workstring = vsoShape.AddNamedRow(visSectionUser, strusercell, visTagDefault)

    'Set vsoCell to the cell just created and set its formula.
       Set vsocell = vsoShape.Cells("user.room")
       strformula = "=guard(sheet.1!user.room)"
       vsocell.Formula = strformula
...
...

Will the code work when a second instance of the master is added? i.e will the reference to sheet.1 be changed to the appropiate sheet number for the additional instances added, or am I out in left field?.  ???


Thanks
Mike

mmulvenna

  • Full Member
  • ***
  • Posts: 91
Re: Making VBA code work for duplicate (multiple instances) shapes
« Reply #1 on: May 12, 2008, 09:14:02 PM »
I haven't tried the "Master" idea yet but since there were no suggestions i will give it a try tomorrow.

Thanks for the views. :-[


Mike

Lars-Erik

  • Sr. Member
  • ****
  • Posts: 278
Re: Making VBA code work for duplicate (multiple instances) shapes
« Reply #2 on: May 13, 2008, 12:08:25 AM »
My guess is: No.
Maybe this will get you in the right direction,
This wont work, each time you drag a shape or group to your visio document it will get an unique name. So the first time it might be Sheet.1 the second it will be something else (Sheet.2?) So i think you'll have to try something with CallThis instead of RunMacro
With the CallThis function the shape will be passed to VBA. then you can use that shape to work with instead of using a fixed shape (Sheet.1).

- Lars-Erik


mmulvenna

  • Full Member
  • ***
  • Posts: 91
Re: Making VBA code work for duplicate (multiple instances) shapes
« Reply #3 on: May 13, 2008, 08:15:26 AM »
My guess is: No.
Maybe this will get you in the right direction,
This wont work, each time you drag a shape or group to your visio document it will get an unique name. So the first time it might be Sheet.1 the second it will be something else (Sheet.2?) So i think you'll have to try something with CallThis instead of RunMacro
With the CallThis function the shape will be passed to VBA. then you can use that shape to work with instead of using a fixed shape (Sheet.1).

- Lars-Erik



Thank you, I will look up the CallThis function and give it a shot.

Mike

Lars-Erik

  • Sr. Member
  • ****
  • Posts: 278
Re: Making VBA code work for duplicate (multiple instances) shapes
« Reply #4 on: May 13, 2008, 08:21:14 AM »
Here is an example of the CallThis function:
http://visguy.com/vgforum/index.php?topic=26.0
And here is the SDK interpretation:
http://msdn.microsoft.com/en-us/library/aa212649(office.11).aspx

mmulvenna

  • Full Member
  • ***
  • Posts: 91
Re: Making VBA code work for duplicate (multiple instances) shapes
« Reply #5 on: May 13, 2008, 12:47:13 PM »
Here is an example of the CallThis function:
http://visguy.com/vgforum/index.php?topic=26.0
And here is the SDK interpretation:
http://msdn.microsoft.com/en-us/library/aa212649(office.11).aspx
Thanks again. I will give it a shot. If I am understanding this correctly, I will also have to use the additional arguments of the CallThis function as I need a number of references to different "sheets" in the called precedure.

Thanks I will play with it to see if my novice experience can get it working. Appreciate all the suggestions. :)

MIke

mmulvenna

  • Full Member
  • ***
  • Posts: 91
Re: Making VBA code work for duplicate (multiple instances) shapes
« Reply #6 on: May 22, 2008, 11:02:26 AM »
I changed all my runmacro functions to the callthis function.  Luckily I only had to change one action cell in sheet.1 as all other sheets action cell formulas reference the action cell formula in sheet.1

In addition I made changes to the VBA code so it did not reference sheet ids and all is working fine.

Thanks to all for the suggestions ;D

Mike