combo box value change

[ManvinderKaur]

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.

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

 

[AlphaFrog]

I'm not sure what you mean by "goes to Error without any error"

Comment out this line...

On Error GoTo Error

...and then what line of code does it error on?

 

[ManvinderKaur]

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]

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...

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

 

[ManvinderKaur]

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.

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