My Three Wishes

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
 
B

Bob Phillips

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)
 
G

gregork

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
 
R

Rob van Gelder

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)?
 
B

Bob Phillips

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>.
 
G

gregork

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
 
Top