combobox cases

[Hannu Rantala]

Hello!

I am using excel and combobox to give alternatives for user.
How to programme following command in VBA:

If comboboxselection = 1 Then
Copy from 'sheet2'!B45:B52 & paste to 'sheet2'!B58:B65

If comboboxselection = 2 Then
Copy from 'sheet2'!C45:B52 & paste to 'sheet2'!B58:B65

If comboboxselection = 3 Then
Copy from 'sheet'!D4552 & paste to 'sheet2'!B52:B65
..
..
..
repeated sufficient times...

Hannu Rantala

 

[Tom Ogilvy]

use a case statement in the click event

Private Sub Combobox1_Click()
Case Combobox1.ListIndex + 1
Case 1
Range("Sheet2!B45:B52").Copy Destination:=Range("Sheet2!B59")
Case 2
Range("Sheet2!C45:C52").Copy Destination:=Range("Sheet2!B59")
Case 3
Range("Sheet2!D4552").Copy Destination:=Range("Sheet2!B59")
Case . . .
End Select
End sub

if the column just shifts over 1 you could use

Private Sub Combobox1_Click()
Dim rng as Range
set rng = Range("Sheet2!B45:B52")
rng.offset(0,combobox1.ListIndex).Copy _
Destination:=Range("Sheet2!B59")
End Sub

 

[Bob Phillips]

Hi Hannu,

With Worksheets("Sheet2")
If Combobox1.ListIndex = 0 Then
.Range("B45:B52 ").Copy Destination:=.Range("B58")
ElseIf Combobox1.ListIndex = 1 Then
.Range("C45:C52 ").Copy Destination:=.Range("B58")
ElseIf Combobox1.ListIndex = 2 Then
.Range("D4552 ").Copy Destination:=.Range("B58")
End If
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Hello

Use the Change Event of the ComboBox and the Select Case
Statement as in the example below

Private Sub ComboBox1_Change()
Dim SelectVal
SelectVal = ComboBox1.Value
Select Case SelectVal
Case 1
Worksheets("Sheet2").Range("B45:B52").Copy
Destination:=Worksheets("Sheet2").Range("B58:B65")
Range("A1").Value = "Number 1 selected"
Case 2
Worksheets("Sheet2").Range("C45:C52").Copy
Destination:=Worksheets("Sheet2").Range("B58:B65")
Range("A1").Value = "Number two chosen"
Case Else
Range("A1").Value = "Numbers 1 and 2 not selected"
End Select
End Sub

Best of luck
Peter Scott

 

[Hannu Rantala]

Thank you Bob. The program works excellent!

Hi Hannu,

With Worksheets("Sheet2")
If Combobox1.ListIndex = 0 Then
.Range("B45:B52 ").Copy Destination:=.Range("B58")
ElseIf Combobox1.ListIndex = 1 Then
.Range("C45:C52 ").Copy Destination:=.Range("B58")
ElseIf Combobox1.ListIndex = 2 Then
.Range("D4552 ").Copy Destination:=.Range("B58")
End If
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)