Solved

Changing Text in a Range Named and Macro Assigned "Box"

Posted on 2013-12-05
3
202 Views
Last Modified: 2013-12-05
EE Pros,

I have a graphic where I'm trying to change the text in the textbox in cell (B2) from "Select an Outcome" to "Select an Area of Optimization".  The problem I have run across is that the text is used in a macro and is also a range name and I cannot figure out how to change the text (I've tried to change the range name and tried to change the text in the Macro).

I'm looking for "how" to do this so I can learn.

Thank you in advance.

B.
Changing-Text-in-a-Range.xlsm
0
Comment
Question by:Bright01
3 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39698113
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim cel                    As Range
   Dim rInitSel               As Range
   Dim rSelections            As Range
   Dim lFreeCells             As Long

   ' input cell to monitor
   Set rInitSel = Range(mc_sINPUT_CELL)

   If Not Intersect(Target, rInitSel) Is Nothing Then

      ' don't act when no valid selection
      If rInitSel.Value = "Select an Outcome" Then Exit Sub

      ' this is the shaded range for the selected outcomes
      Set rSelections = Range(mc_sSELECTED_OUTCOMES)

      On Error Resume Next
      Application.EnableEvents = False

      ' check how many selected outcome cells are empty
      lFreeCells = Application.WorksheetFunction.CountBlank(rSelections)
      If lFreeCells = 0 Then
         ' no cells available - offer to clear
         If MsgBox(prompt:="All available outcome cells are full. Do you wish to clear and start again?", Buttons:=vbYesNo) = vbNo Then
            ' do nothing
         Else
            ' clear the current selections
            ResetPrioritiesAll
            lFreeCells = rSelections.Count
         End If
      End If

      If lFreeCells > 0 Then
         ' get first available output cell
         With rSelections
            Set cel = .Cells(.Count - lFreeCells + 1)
         End With
         cel.Value = rInitSel.Value
      End If
      ' reset B2 to standard text
      rInitSel.Value = "Select an Outcome"
      Application.EnableEvents = True
   End If


End Sub

Open in new window


Replace
      rInitSel.Value = "Select an outcome"

 with

  rInitSel.Value = "Select an Area of Optimization"

and replace

      If rInitSel.Value = "Select an Outcome" Then Exit Sub

with

      If rInitSel.Value = "Select an Area of Optimization" Then Exit Sub


Regards
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698176
I cannot understand what you are looking for!
0
 

Author Closing Comment

by:Bright01
ID: 39698251
Raganzo,

Thank you!  Works now.  Appreciate you letting me know exactly what you did.  I had tried that earlier but had missed the second reference point.

I have another question outstanding; if you get a chance, take a look.

Thanks again,

B.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now