My Three Wishes

  • Thread starter Thread starter gregork
  • Start date Start date
G

gregork

Hi,

*First.* I want to format a range of cells (sheet1 B8:K22) so that whenever
a cell in the range is clicked the colour format,of the partial row the cell
relates to, is changed.
e.g I click on cell D8 and the cells D8:K8 change colour.
If I click on a new cell in a different row then I want the previous
selected row to return to its original colour.

*Second.* When I double click on any cells in the range B8:B22 on my
worksheet (sheet1) I want user form1 to initiate with the text from the cell
I double clicked transposed to combobox1 on my userform. Rob van Gelder
kindly provided a code to achieve this with another worksheet I have. It
works perfectly for that worksheet but I can't seem to get it to work on
this one. I'll post the code at the end of this message.

*Third.* When I select a value from my combo box1 on user form1 I want the
cell the selection refers to (Range=Sheet1 B8:B22 ( + the next 9 cells
across)) highlighted with a colour change or similar. With all of these cell
"highlights" I want the format to return to the original when a new
selection is made.

Regards
gregork


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim frm As UserForm2

Set frm = New UserForm2

frm.ComboBox1.AddItem Target.Value


With frm.ComboBox1: .ListIndex = .ListCount - 1: End With

frm.Show


Set frm = Nothing

End Sub
 
Don't ant much do you<vbg>?

1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B8:K22")) Is Nothing Then
Range("B8:K22").Interior.ColorIndex = xlColorIndexNone
Range(Cells(Target.Row, "B"), Cells(Target.Row,
"K")).Interior.ColorIndex = 35
End If

End Sub

2.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim frm As UserForm2
Dim i As Long

If Not Intersect(Target, Range("B8:B22")) Is Nothing Then
Set frm = New UserForm2

For i = 8 To 22
frm.ComboBox1.AddItem Cells(i, "B").Value
Next i

With frm.ComboBox1: .ListIndex = .ListCount - 1: End With

frm.Show

Set frm = Nothing
End If

End Sub

3.
Private Sub ComboBox1_Change()
With ActiveSheet
Range("B8:K22").Interior.ColorIndex = xlColorIndexNone
Range(Range("B8")(Me.ComboBox1.ListIndex, 1), _
Range("B8")(Me.ComboBox1.ListIndex, 9)).Interior.ColorIndex = 38
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Many thanks Bob your comprehensive reply was much appreciated. I have a few
glitches I'm trying sort out:

Re: 1. When I click outside the range specified the format remains changed.

Re: 2. The double click event is working but the text from my sheet is not
transposing.

Thanks again.

Kind Regards
gregork
 
1.
Move the following line up so it is before the If statement:
Range("B8:K22").Interior.ColorIndex = xlColorIndexNone

2.
Not sure what you mean by transposing...
Bob's code looks good.

Do you see a form appear?
Is your userform named UserForm2?
Is your combobox on UserForm2 named ComboBox1?
Tried deleting ComboBox1 then recreating it (ensure the new combo is called
ComboBox1)?
 
Rob van Gelder said:
1.
Move the following line up so it is before the If statement:
Range("B8:K22").Interior.ColorIndex = xlColorIndexNone


2.
Not sure what you mean by transposing...
Bob's code looks good.
Click to expand...

Of course it does Rob, apart from putting in a loop to do them all, it's
your code<vbg>.
 
Hi Gentlemen,
Thanks for your suggestions Rob.
re: 1. After moving the line the code is working well.
re: 2. Couldn't for the life of me work out why this wasn't working until I
realised the cells in the range B8:B22 were merged cells. I then tried Rob's
original code with the cells unmerged then threw in a bit of the new code
and "Bob's your uncle" it worked.
Many thanks Bob and Rob for your help.

Regards
gregork

*Here's the code I ended up with if it is of any interest:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim frm As UserForm2
Set frm = New UserForm2
If Not Intersect(Target, Range("B8:B22")) Is Nothing Then
Set frm = New UserForm13

frm.ComboBox1.AddItem Target.Value


With frm.ComboBox1: .ListIndex = .ListCount - 1: End With

frm.Show


Set frm = Nothing
End If
End Sub
 
Back
Top