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