Editing text in a SmartArt shape via a drop-down list

[johnve]

Hi everybody!

I am quite new to this whole VBA thing, and I have found myself a tad bit over my head right now.

I am using Excel 2013.

Here is a summary of the attachment, "PracticeTemplate:"

• Contains two sheets, Sheet3 and Sheet4 (sorry if this is confusing!)

◘ Sheet3 is a data matrix, with one section being "Study Questions" and another being "Analytic Efforts"

◘ Sheet4 is a SmartArt hierarchy graphic with four levels. There are two identical drop-down lists located below the bottom left shape.

○ The items in the drop-down lists correspond exactly to the items found in the "analytic efforts" section from Sheet3

My ideal situation would be to select an item from the 'enter' list, press the 'enter' button, and have it populate the corresponding cell in Sheet3 AND place the selected 'analytic effort' text into the SmartArt shape directly above the lists. I also have a 'delete' button that should, you guessed it, clear out both the Sheet3 cell and the text found in the SmartArt shape.

Currently, the macro works perfectly well for both entering and deleting data from the cell in Sheet3. However, if I want multiple items to be in the SmartArt shape, and therefore select multiple items and press 'enter' multiple times, the text in the shape is simply replaced each time. Is there any way to code this in such a way that multiple presses of the 'enter' button will result in multiple items in the shape? Likewise for the 'delete' button?

I apologize if this is a bit much to ask for. I am quite new and can't find much information on programming VBA to manipulate SmartArt graphics.

Thank you,

John

PracticeTemplate.xlsm

[p45cal]

Changing minimally your macros, try:

Sub FillBox()
If Range("N38").Value = "aa" Then Range("Sheet3!H4").Value = "X"
If Range("N38").Value = "bb" Then Range("Sheet3!I4").Value = "X"
If Range("N38").Value = "cc" Then Range("Sheet3!J4").Value = "X"
If Range("N38").Value = "dd" Then Range("Sheet3!K4").Value = "X"
If Range("N38").Value = "ee" Then Range("Sheet3!L4").Value = "X"
If Range("N38").Value = "ff" Then Range("Sheet3!M4").Value = "X"
If Range("N38").Value = "gg" Then Range("Sheet3!N4").Value = "X"
If Range("N38").Value = "hh" Then Range("Sheet3!O4").Value = "X"
If Range("N38").Value = "ii" Then Range("Sheet3!P4").Value = "X"
If Range("N38").Value = "jj" Then Range("Sheet3!Q4").Value = "X"
If Range("N38").Value = "kk" Then Range("Sheet3!R4").Value = "X"
If Range("N38").Value = "ll" Then Range("Sheet3!S4").Value = "X"
If Range("N38").Value = "mm" Then Range("Sheet3!T4").Value = "X"
If Range("N38").Value = "nn" Then Range("Sheet3!U4").Value = "X"

myText = ""
For Each cll In Sheets("Sheet3").Range("H4:U4").Cells
  If cll.Value = "X" Then myText = myText & Sheets("Sheet3").Cells(2, cll.Column).Value & ","
Next cll
myText = Left(myText, Len(myText) - 1)
Dim sha As Shape
Dim sma As SmartArt
Set sha = ActiveWorkbook.ActiveSheet.Shapes("Diagram 1")
With sha
  .Select
  Set sma = .SmartArt
  'sma.AllNodes(4).TextFrame2.TextRange.Text = Range("N38").Value
  sma.AllNodes(4).TextFrame2.TextRange.Text = myText
End With

Range("N38").ClearContents
End Sub

and

Sub EmptyBox()
If Range("N39").Value = "aa" Then Range("Sheet3!H4").ClearContents
If Range("N39").Value = "bb" Then Range("Sheet3!I4").ClearContents
If Range("N39").Value = "cc" Then Range("Sheet3!J4").ClearContents
If Range("N39").Value = "dd" Then Range("Sheet3!K4").ClearContents
If Range("N39").Value = "ee" Then Range("Sheet3!L4").ClearContents
If Range("N39").Value = "ff" Then Range("Sheet3!M4").ClearContents
If Range("N39").Value = "gg" Then Range("Sheet3!N4").ClearContents
If Range("N39").Value = "hh" Then Range("Sheet3!O4").ClearContents
If Range("N39").Value = "ii" Then Range("Sheet3!P4").ClearContents
If Range("N39").Value = "jj" Then Range("Sheet3!Q4").ClearContents
If Range("N39").Value = "kk" Then Range("Sheet3!R4").ClearContents
If Range("N39").Value = "ll" Then Range("Sheet3!S4").ClearContents
If Range("N39").Value = "mm" Then Range("Sheet3!T4").ClearContents
If Range("N39").Value = "nn" Then Range("Sheet3!U4").ClearContents
myText = ""
For Each cll In Sheets("Sheet3").Range("H4:U4").Cells
  If cll.Value = "X" Then myText = myText & Sheets("Sheet3").Cells(2, cll.Column).Value & ","
Next cll
myText = Left(myText, Len(myText) - 1)

Dim sha As Shape
Dim sma As SmartArt
Set sha = ActiveWorkbook.ActiveSheet.Shapes("Diagram 1")
With sha
  .Select
  Set sma = .SmartArt
  'sma.AllNodes(4).TextFrame2.TextRange.Text = Range("A50").Value
  sma.AllNodes(4).TextFrame2.TextRange.Text = myText
End With


Range("N39").ClearContents

End Sub

[johnve]

p45cal,

The code works like a charm, thank you very much!

Is there a resource where I could learn about how to do what you did for me?

Again, thank you

John

[p45cal]

See here: http://www.vbaexpress.com/resources.php
I found books by John Walkenbach to be good.