Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My Three Wishes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
My Three Wishes
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) "gregork" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
My Three Wishes
Many thanks Bob your comprehensive reply was much appreciated. I have a few
glitches I'm trying sort out: 1. When I click outside the range specified the format remains changed. 2. The double click event is working but the text from my sheet is not transposing. Thanks again. Kind Regards gregork "Bob Phillips" wrote in message ... 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) "gregork" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
My Three Wishes
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 - http://www.vangelder.co.nz/excel "gregork" wrote in message ... Many thanks Bob your comprehensive reply was much appreciated. I have a few glitches I'm trying sort out: 1. When I click outside the range specified the format remains changed. 2. The double click event is working but the text from my sheet is not transposing. Thanks again. Kind Regards gregork "Bob Phillips" wrote in message ... 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) "gregork" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
My Three Wishes
"Rob van Gelder" wrote in message ... 1. Move the following line up so it is before the If statement: Range("B8:K22").Interior.ColorIndex = xlColorIndexNone Thanks Rob, that requirement was a bit too subtle for me<g. 2. Not sure what you mean by transposing... Bob's code looks good. Of course it does Rob, apart from putting in a loop to do them all, it's your code<vbg. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
My Three Wishes
Hi Gentlemen,
Thanks for your suggestions Rob. 1. After moving the line the code is working well. 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 "Bob Phillips" wrote in message ... "Rob van Gelder" wrote in message ... 1. Move the following line up so it is before the If statement: Range("B8:K22").Interior.ColorIndex = xlColorIndexNone Thanks Rob, that requirement was a bit too subtle for me<g. 2. Not sure what you mean by transposing... Bob's code looks good. Of course it does Rob, apart from putting in a loop to do them all, it's your code<vbg. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Christmas Wishes | Excel Discussion (Misc queries) |