Author Topic: How do you use a variable instead of "Sheet.1"  (Read 317 times)

0 Members and 1 Guest are viewing this topic.

Requin

  • Jr. Member
  • **
  • Posts: 10
How do you use a variable instead of "Sheet.1"
« on: August 09, 2018, 12:47:54 PM »
First off this site has been a huge help with this project.  Most issues I ran into (being new to coding with visio) were easily found through a search but this one I couldn't find.

What I'm trying to do is make a new shape and make the text of the shape from a formula that is linked to cells in another shape.  All the info and shape creation is done through VBA macro.

Since it won't always be sheet.9 I need to replace that with a variable.  But I get an error when I tried to use ShapeNumMaster which is a string that equals Sheet.9.  The reason I need it linked is when the info changes it will automatically update all text boxes and there is too much info for it to be setup manually.

Code: [Select]
    Set vsoCable = ActivePage.DrawRectangle(3.5, 10, 5.5, 9.5)
    ShapeNUM = Val(Right(vsoCable, Len(vsoCable) - 6))
    Dim UndoScopeID1 As Long
    UndoScopeID1 = Application.BeginUndoScope("Set Text Field")
    Dim vsoCharacters2 As Visio.Characters
    Set vsoCharacters2 = Application.ActiveWindow.Selection.Item(1).Characters
    vsoCharacters2.Begin = 0
    vsoCharacters2.End = 2
    vsoCharacters2.AddCustomFieldU """1""", visFmtNumGenNoUnits
    Application.EndUndoScope UndoScopeID1, True

    Application.ActiveWindow.Page.Shapes.ItemFromID(ShapeNUM).OpenSheetWindow
    Application.ActiveWindow.Shape.CellsSRC(visSectionTextField, 0, visFieldCell).FormulaU = """FC""&ShapeNumMaster!Prop.FDSA&"",""&Sheet.9!Prop.Count1&""-""&sheet.9!Prop.Count2&char(10)&sheet.9!prop.CableType"
    Application.ActiveWindow.Close

Requin

  • Jr. Member
  • **
  • Posts: 10
Re: How do you use a variable instead of "Sheet.1"
« Reply #1 on: August 09, 2018, 01:49:59 PM »
Yeah that is only a small part of the code.  With functions it's going to be about 1500 lines.

I had thought of that or using TheDoc!User.XXXXX but each one of these shapes that stores info (there can be up to 20 or so on a page) and generates all the info will be using the same macro.  And each of those shapes can generate up to 12 additional shapes.  Each shape has 7 variables that have to be updated live.  The max would be 1680 rows which I though was too many for one area but if it's the only way then I'll have to give it a try.

Requin

  • Jr. Member
  • **
  • Posts: 10
Re: How do you use a variable instead of "Sheet.1"
« Reply #2 on: August 09, 2018, 03:12:31 PM »
I have 1 shape setup and it will be placed in a stencil.  Once dragged onto the sheet it will ask for the user to fill in info for the shape data.  After this point there are two macros setup as actions.  First macro generates the other shapes and links all the info.  While this macro is running is requests data from the user on specifics of those shapes when required.  The 2nd macro just runs the calculations again if any numbers have changed in the shape data and updates the info in the shapes.  After that they will drag as many shapes as required from the stencil and go through the process again until everything is finished.

Even if I do store the info in thepage! or thedoc! it is looking like I will have to search the data names to find the right ones to link since I'm using an array setup in which the size changes each time it is run.

Requin

  • Jr. Member
  • **
  • Posts: 10
Re: How do you use a variable instead of "Sheet.1"
« Reply #3 on: August 10, 2018, 02:58:43 PM »
CALLTHIS didn't work :(

Code: [Select]
    Application.ActiveWindow.Shape.CellsSRC(visSectionTextField, 0, visFieldCell).FormulaU = """FC""& Sheet.9" & "!Prop.Types&""MC"""

works perfectly fine.  I've separated the sheet.9 and !Prop.Types.  I have a variable that equals 9 and is an integer (i tried with a string as well) if I replace the 9 I get an error saying bad shapesheet name.


Code: [Select]
    Application.ActiveWindow.Shape.CellsSRC(visSectionTextField, 0, visFieldCell).FormulaU = """FC""& Sheet." & "ShapeNUM" & "!Prop.Types&""MC"""

But if I leave the 9 in the formula and even have that shapeNUM in there it works fine.
Code: [Select]
    Application.ActiveWindow.Shape.CellsSRC(visSectionTextField, 0, visFieldCell).FormulaU = """FC""& Sheet.9" & "ShapeNUM" & "!Prop.Types&""MC"""

Is there something wrong with the sintax that it doesn't pick up the ShapeNUM at all?

Thanks,

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3282
  • Ideas Visio-lized into solutions
Re: How do you use a variable instead of "Sheet.1"
« Reply #4 on: August 10, 2018, 04:05:01 PM »
Well, I went thru your code and it turns out the Field entry is quite fussy.  All those quotes.  Yikes.
I broke down the problem program line, and then started adding elements, getting each new step to work.  It was easier to create a string, "MyString", and build that, rather than mess with the entire program line.  As you can see, MyString has both text strings and variables.  All the elements to build your formula.  I suggest sticking with the MyString approach as it's easier to debug.

Also, you can use CellsU instead of CellsSRC.  Bit simpler to understand.  I included 3 variations of the program line.  They all work.  The active one is my preference.  The 2nd replaces MyString with the literal content.  The 3rd is same as 1st, except uses SRC variant.

Code: [Select]
Sub partial()
    Dim vsoCharacters2 As Visio.Characters

    Set vsoCable = ActivePage.DrawRectangle(3.5, 10, 5.5, 9.5)
    ShapeNUM = Val(Right(vsoCable, Len(vsoCable) - 6))
    ShapeNumMaster = "357"
   
    Set vsoCharacters2 = ActiveWindow.Selection.Item(1).Characters
    vsoCharacters2.Begin = 0
    vsoCharacters2.End = 2
    vsoCharacters2.AddCustomFieldU "1", visFmtNumGenNoUnits
   
    MyString = Chr(34) & "FC" & ShapeNumMaster & "!Prop.FDSA" & Chr(34)
'    Debug.Print MyString

    ActiveWindow.Page.Shapes.ItemFromID(ShapeNUM).OpenSheetWindow
    ActiveWindow.Shape.CellsU("Fields.Value").FormulaU = Chr(34) & Chr(34) & MyString & Chr(34) & Chr(34)
'    ActiveWindow.Shape.CellsU("Fields.Value").FormulaU = Chr(34) & Chr(34) & Chr(34) & "FC" & ShapeNumMaster & "!Prop.FDSA" & Chr(34) & Chr(34) & Chr(34)
'    ActiveWindow.Shape.CellsSRC(visSectionTextField, 0, visFieldCell).FormulaU = Chr(34) & Chr(34) & MyString & Chr(34) & Chr(34)
    ActiveWindow.Close
End Sub

Wapperdude
« Last Edit: August 11, 2018, 11:08:57 AM by wapperdude »
Visio 2007 Std

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3282
  • Ideas Visio-lized into solutions
Re: How do you use a variable instead of "Sheet.1"
« Reply #5 on: August 12, 2018, 06:32:21 PM »
Here's a more condensed code.
Code: [Select]
Sub NewPartial()
    Dim vsoShp As Shape
    Dim vsoCharacters1 As Visio.Characters
    Dim ShapeNumMaster As Variant
    Dim MyString As String
   
    ShapeNumMaster = 357
    MyString = Chr(34) & "FC" & ShapeNumMaster & "!Prop.FDSA" & Chr(34)

    Set vsoShp = ActivePage.DrawRectangle(3.5, 10, 5.5, 9.5)
   
    Set vsoCharacters1 = ActiveWindow.Selection.Item(1).Characters
    vsoCharacters1.Begin = 0
    vsoCharacters1.End = 0
    vsoCharacters1.AddCustomFieldU Chr(34) & Chr(34) & MyString & Chr(34) & Chr(34), 0
End Sub

Visio 2007 Std

Requin

  • Jr. Member
  • **
  • Posts: 10
Re: How do you use a variable instead of "Sheet.1"
« Reply #6 on: August 13, 2018, 07:44:14 AM »
Thanks a ton warpperdude!  Your code didn't give me exactly what I needed but it put me on the right path.  Building the string is still confusing due to it being a formula as well.  The final result looked something like this. 

Code: [Select]
    FieldText = Chr(34) & "FC" & Chr(34) & "&" & ShapeNumMaster & "FDSA" & "&" & Chr(34) & "," & Chr(34) & "&" & ShapeNumMaster & "Count1" & "&" & Chr(34) & "-" & Chr(34) & "&" & ShapeNumMaster & "Count2"
    vsoCharacters2.AddCustomFieldU FieldText, 0

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3282
  • Ideas Visio-lized into solutions
Re: How do you use a variable instead of "Sheet.1"
« Reply #7 on: August 13, 2018, 08:20:50 AM »
Our welcome. Nice to know problem resolved.

Wapperdude
Visio 2007 Std