Question as to Combo box

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
Trying to link combo box chooses with ranges in excel. I have attempted to do a code see below. Please help

VBA Code:
Private Sub Towbar_Index()

Dim Ws      As Worksheet
Dim rng     As Range
Dim cbo     As ComboBox

Set cbo = Me.Towbar_Type

Set Ws = Sheets("Part_Group_Items_ List")


If cbo.Value = "Transit Chassis Cab Towbar - Modified, Taillift " Then
        Sheets Ws.rng("A2:O4").Value
  End If
If cbo.Value = "TIZ4 - Isuzu Towbar - fits all 3.5T models" Then
        Sheets Ws.rng("A12:O14").Value
  End If
If cbo.Value = "Sprinter Chassis Cab Towbar" Then
       Sheets Ws.rng("A20:O22").Value
  End If
If cbo.Value = "Canter Chassis Cab Towbar" Then
        Sheets Ws.rng("A25:O30").Value
End If
If cbo.Value = "Master Chassis Cab Towbar – Single rear wheel - FWD & RWD" Then
        Sheets Ws.rng("A32:O24").Value
End If
If cbo.Value = "Master Chassis Cab Towbar – Twin rear wheel - RWD" Then
        Sheets Ws.rng("A37:O39").Value
  End If
If cbo.Value = "Daily Chassis cab Towbar" Then
        Sheets Ws.rng("A42:O44").Value
End If

If cbo.Value = "Boxer/Ducato/Relay Chassis Cab Towbar" Then
        Sheets Ws.rng("A47:O49").Value
End If

End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
If you want those ranges to equal what is in the combobox then this is how:
If cbo.Value = "TIZ4 - Isuzu Towbar - fits all 3.5T models" Then
Ws.range("A12:O14").Value = cbo.value
End If
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the above. The idea now is to add the above data to a selected row or rows on the new spreadsheet.
I`ve tried to but no joy see below. Not sure how to add an index to the code.

VBA Code:
Private Sub Towbar_Type_Click()

Dim addme As Range
Dim LastRow As Long
Dim wsdest As Worksheet
With Me.Towbar_Type
Set wsdest = ThisWorkbook.Sheets("Job Card Master")
LastRow = Selection.Row

Set addme = wsdest.Range("A" & LastRow)
Call Towbar_Index
ListIndex = Towbar_Index

ck = 0

   For X = 0 To Towbar_Type.ListCount - 1
    
        If Me.Towbar_Type.Selected(X) Then
            ck = 1
          addme.Offset(0, 1) = Me.Towbar_Type.List(X, Towbar_Index)

                 Set addme = addme.Offset(1, 0)
               
            Me.Towbar_Type(X) = False
        End If
    Next X
 
    If ck = 0 Then
        MsgBox "There is nothing selected"
    End If

End With
End Sub
 
Last edited by a moderator:

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
Can't seem to make the below code work.
I am trying to link VBA combo box to ranges then paste selected ranges to new worksheet.
VBA Code:
Private Sub Towbar_Type_Click()

Dim wsCopy      As Worksheet
Dim wsDest      As Worksheet
Dim addme       As Range
Dim Rng         As Range
Dim cbo         As ComboBox
Dim LastRow     As Long
Set cbo = Me.Towbar_Type
Set wsCopy = Sheets("Part_Group_Items_ List")
Set wsDest = ThisWorkbook.Sheets("Job Card Master")
LastRow = Selection.Row



If cbo.Value = "Transit Chassis Cab Towbar - Modified, Taillift " Then
       wsCopy.Range("A2:O4").Value = cbo.Value
  End If
 
If cbo.Value = "TIZ4 - Isuzu Towbar - fits all 3.5T models" Then
       wsCopy.Range("A12:O14").Value = cbo.Value
  End If
 
If cbo.Value = "Sprinter Chassis Cab Towbar" Then
       wsCopy.Range("A20:O22").Value = cbo.Value
  End If
 
If cbo.Value = "Canter Chassis Cab Towbar" Then
        wsCopy.Range("A25:O30").Value = cbo.Value
End If

If cbo.Value = "Master Chassis Cab Towbar – Single rear wheel - FWD & RWD" Then
        wsCopy.Range("A32:O24").Value = cbo.Value
End If

If cbo.Value = "Master Chassis Cab Towbar – Twin rear wheel - RWD" Then
        wsCopy.Range("A37:O39").Value = cbo.Value
  End If
 
If cbo.Value = "Daily Chassis cab Towbar" Then
        wsCopy.Range("A42:O44").Value = cbo.Value
End If

         
If cbo.Value = "Boxer/Ducato/Relay Chassis Cab Towbar" Then
        wsCopy.Range("A47:O49").Value = cbo.Value
End If


Set addme = wsDest.Range("A" & LastRow).Select
Selection.Rng.Paste


End Sub
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
This is how I would change it. The last part where you were pasting data into "Addme" was not done right. I assumed you wanted to paste the value of the combo box also. If I were you, I would create named ranges for the different towbar types and reference those in the macro.

VBA Code:
Private Sub Towbar_Type_Click()

Dim wsCopy      As Worksheet
Dim wsDest      As Worksheet
Dim addme       As Range
Dim Rng         As Range
Dim CBOVal      As String

Set wsCopy = Sheets("Part_Group_Items_ List")
Set wsDest = ThisWorkbook.Sheets("Job Card Master")
CBOVal = TowBar_Type.Value


Select Case CBOVal
  Case "Transit Chassis Cab Towbar - Modified, Taillift "
    wsCopy.Range("A2:O4").Value = CBOVal
  Case "TIZ4 - Isuzu Towbar - fits all 3.5T models"
    wsCopy.Range("A12:O14").Value = CBOVal
  Case "Sprinter Chassis Cab Towbar"
    wsCopy.Range("A20:O22").Value = CBOVal
  Case "Canter Chassis Cab Towbar"
    wsCopy.Range("A25:O30").Value = CBOVal
  Case "Master Chassis Cab Towbar – Single rear wheel - FWD & RWD"
    wsCopy.Range("A32:O24").Value = CBOVal
  Case "Master Chassis Cab Towbar – Twin rear wheel - RWD"
    wsCopy.Range("A37:O39").Value = CBOVal
  Case "Daily Chassis cab Towbar"
    wsCopy.Range("A42:O44").Value = CBOVal
  Case "Boxer/Ducato/Relay Chassis Cab Towbar"
    wsCopy.Range("A47:O49").Value = CBOVal
End If


Set addme = wsDest.Range("A" & wsDest.Rows.Count).End(xlUp).Offset(1, 0)
addme.Value = CBOVal


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,126,980
Messages
5,621,951
Members
415,869
Latest member
LWSkinner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top