Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-12-05
3
Medium Priority
?
250 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 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

916 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