Swap around Equal Sign

[kpark91]

Hello, I am currently using VBA code that requires a variable be set to a userform object's value.

    ws.Range("A1").Value = Me.a.Text
    ws.Range("B1").Value = Me.b.Text
    ws.Range("C1").Value = Me.c.Text
    ws.Range("D1").Value = Me.d.Text
    .
    .

They have been copy+pasted onto a excel spreadsheet into columnA and there are quite alot of these tedious lines.

Now, I need a formula (preferably) to swap these around the equal sign.
and show it in columnB.

So, it would look like

    Me.a.Text = ws.Range("A1").Value   
    Me.b.Text = ws.Range("B1").Value
    Me.c.Text = ws.Range("C1").Value 
    .
    .

Thank you in advance,
Kpark.

PS: There will only be one equal sign so there will be no "=" in any of the codelines I want to swap.
For example,

None of these
Worksheets("=Hello").Range("A1").Value = "Blarg="

 

[xenou]

If by "they have been copy+pasted onto a excel spreadsheet into columnA" you mean that the lines are all in Excel now, then

Assuming in A1 is:
ws.Range("A1").Value = Me.a.Text
In B1 use formula:
=LEFT(A1,FIND("=",A1)-1)&" = "&RIGHT(A1,LEN(A1)-FIND("=",A1))

ξ

 

[AlphaFrog]

    Dim cell As Range
    For Each cell In ws.Range("[COLOR="Red"]A1:C1[/COLOR]")
        Me.Controls(Replace(cell.Address(0, 0), "1", "")).Value = cell.Value
    Next cell

 

[Rick Rothstein]

Assuming those code lines have been copied into Column A of the active sheet, you can do the reversal you want using this formula...

=MID(A1&" = "&A1,FIND("=",A1)+2,LEN(A1))

You can then Copy/PasteSpecial using the Values option to put the actual text into a column which you can then Copy/Paste into your code window.

 

[kpark91]

Awesome.
Thank you for all the answers.
and sorry for the late reply (just got back from lunch).

I've only tried Xenou's solution and it worked.

Thank you Xenou, Alpha and Rick.

 

[Greg Truby]

It looks like you're already in very capable hands regarding the question you posted, so I'll add some thoughts aimed at a broader level. If you are trying to ride herd on a large number of controls, you can utilize each control's tag property to store relationship data as additional "properties". For example you can put something like the following in the tags of three textbox controls:

• type=linked;link=ProdQty
• type=linked;link=Price
• type=linked;link=ProdNbr

The values for the "link" property would be the names of named ranges (much more robust than referencing addresses).

At a basic level you then loop the controls, checking for tags of non-zero length, and when you find one, parse it and use the parsed info to run your initializations at startup and your transfers back to the sheet when the "OK" button is clicked.

If you have need of a variety of "custom properties" for lots of controls, you can create a class module that ties out to a collection of controls (Ken Getz has a Tag class module he developed for use in Access), but you can roll your own if need be. That's a fairly big step, though.

An architecture like this is much more scalable, maintainable and robust. It's a question of bang for the buck. If you only have a handful of control/cell pairings, then it might not be worth the work. But if your form has dozens of controls, it may be worth exploring.

 

[kpark91]

Hello, Greg.
Thank you for the excellent suggestion.
I would love to look into it and learn it as maintainability is probably the most important factor of programming as majority of the time/money can be spent on it if the code is unreadable.

I've tried to search it up on an engine but I can't seem to find the utilization of tag property as you've suggested.

I am sorry to be so bothersome but is there a good tutorial on linking with cells using tag property?
or just one snippet of code that links one cell with one control?

PS: Please let me know if I should start another thread.

Thank you in advance,
KPark.

 

[Rick Rothstein]

You may have misinterpretted the structure that Greg used for some kind of functionality. There is nothing "magical" about the Tag property of a control... it simply stores text, any text you want to put in it, and nothing more. Greg was just showing you that you can "structure" the text in such a way that you can later parse it and then do something with that parsed text. For example, Greg's first example...

type=linked;link=ProdQty

is making the assumption that later on, when you revisit the control (textbox in this case) to do something with it, you can query the control's tag property to see what additional information about it you stored there. Here, Greg assumed you would want to know whether the control was linked or not and, if linked, what named range contained the cell address of the link. Your code must parse this text string to pull out the parts you want and then react to them. Here you could Split on the semi-colon and then Split the zeroeth and first elements on the equal sign to get the properties and values of those properties that you stored in the Tag property for this later parsing.

 

[kpark91]

Oh you're right.
I have completely misinterpreted the structure.

Now I understand for sure!
Thank you for the clarification Rick.

Kpark.

 

[Greg Truby]

Since you're the person that started the thread and since the lads already have your initial question sorted out, I think we're fine to just keep going on this thread. It's a pretty broad topic, really. I first learned it from the book Access 2002 Developer's Guide by Paul Litwin, Ken Getz & Mike Gunderloy. Pages 302-304, specifically. Here's a bit of code the a project from about eight years ago...

If you look at almost any control you'll see that they have a TAG property. In my form I had hundreds of controls. Among one set I had two groups of textboxes decribing inputs and the tags looked like this:

Group=In2;Row=6;Save=In;Type=Name;

Here are some lines from the form's Initialize event handler. You can see that the tv class object has both text and item properties that allows one to quickly parse and access the various "custom properties" embedded in the tag's value. (You set the tag's value at design time when setting up the form.)

Private Sub UserForm_Initialize()
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
    Dim rngDataBlock As Range, strHeaders() As String, strMsg As String
    Dim ctl As MSForms.Control, tv As TaggedValues
    Dim i%, intCbxCount%
    Dim cb As ComboBox
 
{snip}
 
    If booInitialized Then Exit Sub
 
{snip}
 
    Set tv = New TaggedValues
 
{snip}
 
    ' _____Hide Controls for Fields 6 to 10_____
 
    For Each ctl In Me.Controls
        tv.text = ctl.Tag
        If tv.Item("Group") = "In2" Then
            ctl.Visible = False
        ElseIf tv.Item("Group") = "In1" Then
            ctl.Visible = True
        End If
    Next ctl
    Set ctl = Nothing
    Set tv = Nothing
{snip}

I apologize, but I don't have time to write up a fuller explanation of the concept. Perhaps Rick or David are also familiar with this concept/solution and can add to it?