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!D45:D52").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

--
Regards,
Tom Ogilvy

"Hannu Rantala" wrote in message
om...
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'!D45:D52 & paste to 'sheet2'!B52:B65
.
.
.
repeated sufficient times...

Hannu Rantala