combo box value change

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
Hi I have two combo boxes cboBaseline and cbobaseline and one command button cmdComparision
when people select value from both combo boxs and then press click my sub for "Private Sub cmdComparision_Click() " goes to "Private Sub cboBaseline_Change()" sub each time values copy from baseline or re-assessment sheet to comparision sheet. My code for Private Sub cmdComparision_Click() also goes to Error without any error. I could not figure these out.


Code:
Private Sub cboBaseline_Change()
    cboReass.Value = ""
End Sub
 
Private Sub cboReass_Change()
    cboReass.Value = Format(cboReass.Value, "dd-mmm-yyyy")
End Sub
 
Private Sub cmdComparision_Click()
Dim BaselineRange As Integer
Dim ReassRange As Integer
Dim i As Integer
Dim BCheck As Boolean
Dim RCheck As Boolean
On Error GoTo Error
If cboBaseline.Value = "" Then
    MsgBox ("Please select Baseline Business name")
Exit Sub
ElseIf cboReass.Value = "" Then
    MsgBox ("Please select Reassessment date")
    Exit Sub
End If
    BCheck = False
    RCheck = False
    For i = 4 To Worksheets("Baseline").Range("A" & Rows.Count).End(xlUp).Row
    If Worksheets("Baseline").Range("A" & i).Value = cboBaseline.Value Then
    BCheck = True
    BaselineRange = i
    End If
    Next i
    
    For i = 4 To Worksheets("Re-assessment").Range("A" & Rows.Count).End(xlUp).Row
    If Format(Worksheets("Re-assessment").Range("F" & i).Value, "dd-mmm-yyyy") = cboReass.Value And Worksheets("Re-assessment").Range("A" & i).Value = cboBaseline.Value Then
    RCheck = True
    ReassRange = i
    End If
    Next i
    
    If BCheck = False Or RCheck = False Then
    MsgBox ("Please select values from drop down")
    Exit Sub
    End If
        
    Application.ScreenUpdating = False
    Worksheets("Comparision").Range("B9").Value = Worksheets("Baseline").Range("A" & BaselineRange).Value
    Worksheets("Comparision").Range("B10").Value = Worksheets("Baseline").Range("I" & BaselineRange).Value
    Worksheets("Comparision").Range("C35").Value = Worksheets("Re-assessment").Range("U" & ReassRange).Value
    Worksheets("Comparision").Range("C36").Value = Worksheets("Re-assessment").Range("V" & ReassRange).Value
    Worksheets("Comparision").Range("C37").Value = Worksheets("Re-assessment").Range("W" & ReassRange).Value
    Worksheets("Comparision").Range("C38").Value = Worksheets("Re-assessment").Range("Y" & ReassRange).Value
    Worksheets("Comparision").Range("C39").Value = Worksheets("Re-assessment").Range("X" & ReassRange).Value
    Worksheets("Comparision").Range("C40").Value = Worksheets("Re-assessment").Range("Z" & ReassRange).Value
    Worksheets("Comparision").Range("C42").Value = Worksheets("Re-assessment").Range("AA" & ReassRange).Value
    Worksheets("Comparision").Range("C43").Value = Worksheets("Re-assessment").Range("AB" & ReassRange).Value
    Worksheets("Comparision").Range("C44").Value = Worksheets("Re-assessment").Range("AC" & ReassRange).Value
    Worksheets("Comparision").Range("C45").Value = Worksheets("Re-assessment").Range("AD" & ReassRange).Value
    Worksheets("Comparision").Range("Q10").Value = Worksheets("Re-assessment").Range("C" & ReassRange).Value
    Worksheets("Comparision").Range("Q11").Value = Worksheets("Re-assessment").Range("D" & ReassRange).Value
    Worksheets("Comparision").Range("Q12").Value = Worksheets("Re-assessment").Range("E" & ReassRange).Value
    Worksheets("Comparision").Range("Q13").Value = Worksheets("Re-assessment").Range("AF" & ReassRange).Value
    Application.ScreenUpdating = True
Error:
If Err.Number = 1004 Then
MsgBox ("Please select Baseline business name and Re-assessment date values from drop down")
End If
End Sub
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
I'm not sure what you mean by "goes to Error without any error"

Comment out this line...
Code:
On Error GoTo Error
...and then what line of code does it error on?
 

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
okay but whenever my code is at
Worksheets("Comparision").Range("G10").Value = Worksheets("Baseline").Range("H" & BaselineRange).Value
then to
Private Sub cboBaseline_Change()
cboReass.Value = ""
End Sub
then to
Worksheets("Comparision").Range("G11").Value = Worksheets("Baseline").Range("K" & BaselineRange).Value
then again
Private Sub cboBaseline_Change()
cboReass.Value = ""
End Sub
then next line then again
Private Sub cboBaseline_Change()
cboReass.Value = ""
End Sub
II have no idea why each time
Private Sub cboBaseline_Change()
cboReass.Value = ""
End Sub runnes
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
Is the value in your combobox cboBaseline linked to the cell Worksheets("Comparision").Range("G10")?

If it is, then when you change the value in G10, that will trigger the event change for cboBaseline and run the code Private Sub cboBaseline_Change()

If yes, maybe try something like this...

Code:
Application.EnableEvents = False
Worksheets("Comparision").Range("G11").Value = Worksheets("Baseline").Range("K" & BaselineRange).Value
Application.EnableEvents = True
 

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
my values are not linked.
I have three sheets.
first sheet Baseline
2nd sheet Reassessment and
3rd comparision
I want to select business name that will match first coloumn in baseline
and then reassessment date for 2nd combobox that ll match with date from reassessment sheets.
then copy selected data from baseline and reassement to comparision sheet.

defined ranges
BaselineBusinessName =OFFSET(Baseline!$A$4,0,0,COUNTA(Baseline!$A:$A)-2,1)
ReassStartDate=OFFSET('Re-assessment'!$A$4,0,0,COUNTA('Re-assessment'!$A:$A)-2,6)
I am using following code as well for combo boxes.
I have tried your last suggestions. But still not working. I do not have any linked cell in these spreadsheets.
Code:
Private Sub cboBaseline_Gotfocus()
    cboBaseline.ListFillRange = "BaselineBusinessName"
End Sub
'combo box drop down values from Reassessment corresponding to Baseline business name
Private Sub cboReass_Gotfocus()
On Error GoTo Error
lngRowStart = Application.Match(cboBaseline.Value, Sheet5.Range("ReassStartDate").Resize(, 1), 0)
lngCountEntries = Application.CountIf(Sheet5.Range("ReassStartDate").Resize(, 1), cboBaseline.Value)
cboReass.ListFillRange = "'Re-assessment'!" & Sheet5.Range("F" & lngRowStart + 3 & ":F" & lngCountEntries + 3 + lngRowStart - 1).Address
Error:
If Err.Number = 13 Then
cboReass.ListFillRange = ""
End If
End Sub
'Set date format for cboReass
Private Sub cboReass_Change()
    cboReass.Value = Format(cboReass.Value, "dd-mmm-yyyy")
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,118
Messages
5,599,817
Members
414,341
Latest member
Mohammedsobhey

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