Selecting excel cell makes check appear

September 30, 2010 at 13:32:08
Specs: Windows Vista
Hi, I am trying to to get a bullet to appear in a cell when it is selected. If a cell is selected in the same column the first bullet or check should disappear and a new one appear in the newly selected cell. I only want this to operate for C145:V164 and for each column in this range (C-V)
Any Help greatly appreciated. Thanks.

John


See More: Selecting excel cell makes check appear

Report •


#1
September 30, 2010 at 13:48:19
What exactly do you mean by "get a bullet to appear in a cell when it is selected"?

Do these cells have data in them and the "bullet" should appear along with the data? Are the cells empty and should contain just a bullet when selected? Will the bullet be used for something once it is the cell?

Please give us some details as to what you are trying accomplish.


Report •

#2
September 30, 2010 at 14:02:56
The cells are blank with no data. The columns and rows have labels and the bullets are being used to match the column with the row. I will use if statements elsewhere in the worksheet that reference the cells with bullets if that's possible.

Report •

#3
September 30, 2010 at 14:04:41
I could just do a drop down menu with "Yes" or "No" but this way would be quicker and cleaner.

Report •

Related Solutions

#4
September 30, 2010 at 14:59:58
So something like this?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Const targetRange As String = "C145:V164"
  Dim selectedRange As Range
  Set selectedRange = Intersect(Range(targetRange), Target)
  If selectedRange Is Nothing Then _
    Exit Sub
  
  Range(targetRange).ClearContents
  selectedRange = "*"
End Sub


Report •

#5
September 30, 2010 at 15:09:25
Yes except each column has one bullet. When you click in the next column the previous column keeps its bullet. Thanks!

Report •

#6
September 30, 2010 at 15:11:32
Multiple bullets but only one per column.

Report •

#7
September 30, 2010 at 15:25:29
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim targetRange As Range, selectedRange As Range, col As Range
  Set targetRange = Range("C145:V164")
  Set selectedRange = Intersect(targetRange, Target)
  If selectedRange Is Nothing Then _
    Exit Sub
  
  For Each col In selectedRange.EntireColumn
    Intersect(col, targetRange).ClearContents
  Next 'col
  selectedRange = "*"
End Sub


Report •

#8
September 30, 2010 at 15:31:03
Awesome! Perfect

Report •

#9
September 30, 2010 at 21:11:25
Awesome! Perfect...unless the user selects more than one cell. If that happens, you get lots of bullets.

You just can't trust those darn users to do what you want them to!

I tweaked Razor2.3 code so that it only deals with the upper left cell of the selected range.

Another option is to simply Exit if more than one cell is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim targetRange As Range, selectedRange As Range, col As Range
  Set targetRange = Range("C145:V164")
  Set selectedRange = Intersect(targetRange, Target.Cells(1))
  If selectedRange Is Nothing Then _
    Exit Sub

  For Each col In selectedRange.Cells(1).EntireColumn
    Intersect(col, targetRange).ClearContents
  Next 'col
      
  selectedRange.Cells(1) = "*"
End Sub



Report •

#10
October 1, 2010 at 13:47:18
Great. Thanks guys. And to delete a bullet by selecting a cell with a bullet?

Report •

#11
October 1, 2010 at 16:55:46
Here's a little tip when requesting help in a forum such as this:

Now don't take this the wrong way, but we often get questions in this forum where requirements come in drips and draps.

"I need a macro to this"

"Thanks, now can you make it do this too?"

"Hey, that's great. Now, how do I add this?"

That gets a bit annoying since we have to go back and modify the code, sometimes even rewriting it completely so we don't end up with inefficient, cobbled together code.

In addition, we (well, at least I) don't save every workbook that we set up to test our code. When additional requirements come along later, we (I) usually have to copy the code from the forum, set up the workbook with data in the correct columns, etc.

Since I don't want to just add lines to code without testing it, I have to go through extra (repeated) work to test it.

Think about managing a project and asking someone to quote on it or even implement it. Once you start adding requirements after the initial work has begun, things get messy - and usually more expensive.

That said, try this modified version of the code - and keep in mind that I just went through all of the extra steps I detailed above to make sure it works.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim targetRange As Range, selectedRange As Range, col As Range
  Set targetRange = Range("C145:V164")
  Set selectedRange = Intersect(targetRange, Target.Cells(1))
  If selectedRange Is Nothing Then _
    Exit Sub
    
  If selectedRange.Cells(1) = "*" Then
      selectedRange.Cells(1) = ""
      Exit Sub
  End If

  For Each col In selectedRange.Cells(1).EntireColumn
    Intersect(col, targetRange).ClearContents
  Next 'col
      
 selectedRange.Cells(1) = "*"
End Sub


Report •

Ask Question