Aussiebear
04-08-2008, 02:59 AM
In the attached workbook, consisting of two sheets "Risk Levels" & "Test History", I record the latest residue results. Due to an operational requirement, I now need the VendorInfo form to record the new vendor data to both sheets. Access to this form is gained by clicking the Vendor Info button on the "Risk Levels" sheet.
But there's a catch.... ( isn't there always you say). Whilst the I require all of the forms data to be inserted into the "Risk Levels" sheet, I only require the Vendor's Name and Grower ID to be placed into the Test History sheet.
Note: The vendor's Name needs to be inserted in alphabetical order. Password to code is "Shona"
The original code was as follows:
Private Sub cmdAdd_Click()
Dim c As Range, cel As Range
'Save Form contents before changing rows:
Set c = Columns(1).Find(txtVendorName)
If c Is Nothing Then
For Each cel In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
If cel > txtVendorName Then
cel.Resize(2, 13).Insert
cel.Resize(2, 13).Copy
cel.Offset(-2).PasteSpecial xlPasteFormats
ActiveWindow.ScrollRow = cel.Offset(-2).Row
Application.CutCopyMode = False
Exit For
End If
Next
'clear the form for user to add new vendor:
cel.Offset(-2).Select
SaveRow ActiveCell
End If
cmdAdd.Visible = False
txtVendorName.SetFocus
End Sub
So in desperation I thought.... I'd just double up some of the code, and place them into new sub's and then just call them.
Private Sub cmdAdd_Click()
Dim c As Range, cel As Range
'Save Form contents before changing rows:
'Add Vendor details to Risk Levels Sheet
Call AddToRiskLevels
'Add Vendor Name & VendorID to Test History Sheet
Call AddToTestHistory
'clear the form for user to add new vendor:
cel.Offset(-2).Select
SaveRow ActiveCell
cmdAdd.Visible = False
txtVendorName.SetFocus
End Sub
Private Sub AddToRiskLevels()
Dim c As Range, cel As Range
With RiskLevels
Set c = Columns(1).Find(txtVendorName)
If c Is Nothing Then
For Each cel In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
If cel > txtVendorName Then
cel.Resize(2, 13).Insert
cel.Resize(2, 13).Copy
cel.Offset(-2).PasteSpecial xlPasteFormats
ActiveWindow.ScrollRow = cel.Offset(-2).Row
Application.CutCopyMode = False
Exit For
End If
Next
End If
End With
End Sub
Private Sub AddToTestHistory()
Dim c As Range, cel As Range
Dim Grower As String
Dim GrowerID As String
With RiskLevels
Set c = Columns(1).Find(txtVendorName)
If c Is Nothing Then
For Each cel In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
If cel > txtVendorName Then
cel.Resize(2, 2).Insert
cel.Resize(2, 2).Copy
cel.Offset(-2).PasteSpecial xlPasteFormats
ActiveWindow.ScrollRow = cel.Offset(-2).Row
Application.CutCopyMode = False
Exit For
End If
Next
End If
End With
End Sub
and the end result is...I've stuffed it up big time.
But there's a catch.... ( isn't there always you say). Whilst the I require all of the forms data to be inserted into the "Risk Levels" sheet, I only require the Vendor's Name and Grower ID to be placed into the Test History sheet.
Note: The vendor's Name needs to be inserted in alphabetical order. Password to code is "Shona"
The original code was as follows:
Private Sub cmdAdd_Click()
Dim c As Range, cel As Range
'Save Form contents before changing rows:
Set c = Columns(1).Find(txtVendorName)
If c Is Nothing Then
For Each cel In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
If cel > txtVendorName Then
cel.Resize(2, 13).Insert
cel.Resize(2, 13).Copy
cel.Offset(-2).PasteSpecial xlPasteFormats
ActiveWindow.ScrollRow = cel.Offset(-2).Row
Application.CutCopyMode = False
Exit For
End If
Next
'clear the form for user to add new vendor:
cel.Offset(-2).Select
SaveRow ActiveCell
End If
cmdAdd.Visible = False
txtVendorName.SetFocus
End Sub
So in desperation I thought.... I'd just double up some of the code, and place them into new sub's and then just call them.
Private Sub cmdAdd_Click()
Dim c As Range, cel As Range
'Save Form contents before changing rows:
'Add Vendor details to Risk Levels Sheet
Call AddToRiskLevels
'Add Vendor Name & VendorID to Test History Sheet
Call AddToTestHistory
'clear the form for user to add new vendor:
cel.Offset(-2).Select
SaveRow ActiveCell
cmdAdd.Visible = False
txtVendorName.SetFocus
End Sub
Private Sub AddToRiskLevels()
Dim c As Range, cel As Range
With RiskLevels
Set c = Columns(1).Find(txtVendorName)
If c Is Nothing Then
For Each cel In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
If cel > txtVendorName Then
cel.Resize(2, 13).Insert
cel.Resize(2, 13).Copy
cel.Offset(-2).PasteSpecial xlPasteFormats
ActiveWindow.ScrollRow = cel.Offset(-2).Row
Application.CutCopyMode = False
Exit For
End If
Next
End If
End With
End Sub
Private Sub AddToTestHistory()
Dim c As Range, cel As Range
Dim Grower As String
Dim GrowerID As String
With RiskLevels
Set c = Columns(1).Find(txtVendorName)
If c Is Nothing Then
For Each cel In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
If cel > txtVendorName Then
cel.Resize(2, 2).Insert
cel.Resize(2, 2).Copy
cel.Offset(-2).PasteSpecial xlPasteFormats
ActiveWindow.ScrollRow = cel.Offset(-2).Row
Application.CutCopyMode = False
Exit For
End If
Next
End If
End With
End Sub
and the end result is...I've stuffed it up big time.