Solved

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

Posted on 2013-12-05
3
218 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 49

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

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…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

813 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

14 Experts available now in Live!

Get 1:1 Help Now