Grabbing pre-existing "wildcard" text from drawing back into Userform_initialize

[anico]

Hi!

I'm stumped again - this time on something a little more complicated.

I have a network diagram with shapes that have text in their .Text property. When opening a UserForm after it's been unloaded from memory, I want it to grab the text from the appropriate shapes and re-enter it into the matching textboxes on the UserForm. I know I can do this in the UserForm_Initialize() event.

I succeeded in doing this for some objects, where I needed to grab all of the text from that shape, or just a substring (mid), but now I am stumped on how to grab a "variable" substring of the text.

I am trying to grab the numberHere portion of strings that look like one of these three possibilities:
numberHere Gbps Ethernet,  numberHere Gbps Fiber, or numberHere Gbps Crossover.

I would be able to do this just fine with a left() function if the number was always going to be the same amount of characters, but it is not. It could be 1, 2, or 3 digits.


For some more clarification, here is a picture of the UserForm design. I've been instructed that I need to keep it looking like this, and that is why I need to pull the numberHere portion from the text to put only that part in the textboxes.



As you can see, the numberHere portion goes in the textbox, the user chooses Mbps or Gbps in the option buttons, and the combo boxes holds the Ethernet, fiber, or crossover values. I concatenate them all into one string, where I then set the .Text properties of the shapes as those strings. Here is a picture of what I mean on the diagram.



Where this is what the user would have inputted into the UserForm in the first place to get the result shown in the picture above:



I would greatly appreciate whatever sort of advice you've got. I realize there are probably other ways to accomplish a similar result by restructuring how the data is entered in the first place, but I do not have an option to change the format of the UserForm and how the user enters the data.

Thank you very much!

[wapperdude]

Couple ideas...

You can try the SPLIT function,  see https://www.experts-exchange.com/articles/1480/How-to-Split-a-String-with-Multiple-Delimiters-in-VBA.html

2nd, you could change your methodology.  Take the user form values and stuff them into separate shapesheet place holders, e.g., user.numBers.  Then,  catenate these placeholders using field insert....or, you could still  your present approach.  This way, you have separate, distinct quantities to  for your re-population.  Your code could create the placeholders as necessary, so no manual editing and no special shapes would be necessary.

Wapperdude

[anico]

Thanks for the reply Wrapperdude.

So I am trying out the Split() function, and in my testing with a single array, I could get it to work, but now I am getting all sorts of errors in trying to get the arrays to work...

I have 31 fields to retreive data from and repopluate into the UserForm. Some of those fields function off of the same arrays, if that makes sense,so there are actually only 18 unique arrays. My issue is that I keep getting a run-time error (9) where it is saying subscript out of range.

At first I thought this was because some of the shapes' text fields were empty, thus filling in empty strings into some of the arrays' split() functions, so I made sure to fill every field with text. I thought for sure that was the issue, and that it would work now, but I am still getting the error.

Might you know why the vba editor is throwing this error still? I had this same error in a different context before. In the debugger, it highlights the macro which opens my userform ( UserForm2_Open() ). It turned out in that instance, it would highlight that, but the actual issue was because of some form of typo or reference to something that didn't exist in a totally different module - the UserForm_Initialize() module.

Here is my relevent code. It may look absurdly overlycomplicated, but I started coding this project far before I understood Visio more.

Code Select Expand

'Declares Arrays used for Repopulating Textboxes on UserForm2
Dim Result1Array() As String
Dim Result2Array() As String
Dim Result3Array() As String
Dim Result4Array() As String
Dim Result5Array() As String
Dim Result6Array() As String
Dim Result7Array() As String
Dim Result8Array() As String
Dim Result9Array() As String
Dim Result10Array() As String
Dim Result11Array() As String
'----------------------------
Dim Result15Array() As String
Dim Result16Array() As String
'----------------------------
Dim Result24Array() As String
Dim Result25Array() As String
Dim Result28Array() As String
Dim Result29Array() As String
'Parses Arrays for Numeric Values (Textbox Values)
Result1Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(11).Text)
Result2Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(30).Text)
Result3Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(8).Text)
Result4Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(40).Text)
Result5Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(43).Text)
Result6Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(502).Text)
Result7Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(503).Text)
Result8Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(501).Text)
Result9Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(500).Text)
Result10Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(534).Text)
Result11Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(1).Text)
'-------------------------------------------------------------------------------
Result15Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(544).Text)
Result16Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(545).Text)
'-------------------------------------------------------------------------------
Result24Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(746).Text)
Result25Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(747).Text)
Result28Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(744).Text)
Result29Array() = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(748).Text)
'Loads Values back into Textboxes
txt1_1.Text = Result1Array(0)
txt1_2.Text = Result2Array(0)
txt1_3.Text = Result3Array(0)
txt1_4.Text = Result4Array(0)
txt1_5.Text = Result5Array(0)
txt1_6.Text = Result6Array(0)
txt1_7.Text = Result7Array(0)
txt1_8.Text = Result8Array(0)
txt1_9.Text = Result9Array(0)
txtOptionalP2P1.Text = Result10Array(0)
txtOptionalBackup1.Text = Result11Array(0)
'----------------------------
txt2_1.Text = Result1Array(0)
txt2_2.Text = Result2Array(0)
txt2_3.Text = Result3Array(0)
txt2_4.Text = Result15Array(0)
txt2_5.Text = Result16Array(0)
txt2_6.Text = Result6Array(0)
txt2_7.Text = Result7Array(0)
txtOptionalP2P2.Text = Result10Array(0)
txtOptionalBackup2.Text = Result11Array(0)
'----------------------------
txt3_1.Text = Result1Array(0)
txt3_2.Text = Result2Array(0)
txt3_3.Text = Result3Array(0)
txt3_4.Text = Result24Array(0)
txt3_5.Text = Result25Array(0)
txt3_6.Text = Result6Array(0)
txt3_7.Text = Result7Array(0)
txt3_8.Text = Result28Array(0)
txt3_9.Text = Result29Array(0)
txtOptionalP2P3.Text = Result10Array(0)
txtOptionalBackup3.Text = Result11Array(0)

[wapperdude]

It looks like you haven't set the size of your arrays.  See https://msdn.microsoft.com/en-us/library/aa264519(v=vs.60).aspx

Wapperdude

[wapperdude]

Here's article of interest:  http://www.quepublishing.com/articles/article.aspx?p=712186&seqNum=4

Wapperdude

[anico]

Hi again Wrapperdude,

I forgot to mention I was actually getting two different errors. Run-time 9 and run-time 13.

The first time I got the type 9 error I did exactly what you suggested and declared the size of my arrays. However after  I did that I just started to get the type 13 error instead. Now I looked this up and its telling me because it's of a type mismatch, to which I do not know what could possibly be mismatching. I cut the code to only 3 arrays for now, here's what I've got:

Code Select Expand

Dim Result1(9) As String
Dim Result2(9) As String
Dim Result3(9) As String
Result1(0) = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(11).Text)
Result2(0) = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(30).Text)
Result3(0) = Split(Application.ActiveWindow.Page.Shapes.ItemFromID(8).Text)
txt1_1.Text = Result1(0)
txt1_2.Text = Result2(0)
txt1_3.Text = Result3(0)

The only thing I could think of for a data mismatch was the array type, so I've tried declaring them as Variant types and that results in the exact same error. When the debugger runs, this error also highlights the open userform macro as the culprit and obviously that is wrong.

Might you have an idea what's wrong? Today marks day number 2 of hitting this roadblock.

Thank you!

[anico]

Edit: I figured it out. I was accidentally trying to set one index of the whole array as the greater value of the entire .Text
That, and apparently Visio 2003 has some native bugs with throwing errors that don't actually exist and all I had to do was close/reopen my current project.

[wapperdude]

Ok.

Generally, working with text in Visio, one uses "characters".  Sort of weird.  But, that's not to say, "text" doesn't work, it just not preferred, and does fail sometimes.

I've attached a simple Visio file to demonstrate (and confuse  ).  The one line that is commented out, will produce an error.  The file does two things:  1) shows various (but not all) text handling methods, and 2) demonstrates how you can take small portions of code and do simple tests to resolve errors.

One last question, when you say debugger running, do you mean stepping thru the code sequentially, line-by-line using the <F8> key.  That is a good way to find specific code errors.  Some errors may prevent the code from running, though, and you never get to step into it.

Wapperdude

[anico]

Thanks for the file to look at, that clarifies more 

When I say running the debugger, I mean when it first brings up the error window, clicking debug, and simply looking at the first (and only) thing it highlighted. I could not get the F8 step-by-step debugger to work in this context, I assume that was because it was saying that there was a problem in a totally different module. That or maybe I simply wasn't trying it correctly.

[wapperdude]

To use F8, you need to be in the VBA edit window, <alt>+F11.   Then, you need to put  cursor somewhere in the starting sub.  Now press F8.  Note, if you begin at some  sub that is "called", you may get error because it'll be lacking some info.

You can also run subs from the window.  You can introduce break points to halt execution...press cursor on the strip to the left of the code.  Should  a dark red highlight plus a dot in  strip.

If you plan to go into this, you really do need  research VBA more. 

Wapperdude