Hey Guys,
Im new to VBA and Im having trouble with a project Im working on.
I've created a "database" within excel that you populate with a Form.
I've got most of the functions working, but Im hung up on one bit.
The logic of the Form entry works like this:
When the Form CommandButton is pressed, it searches the data table that the form populates for criteria in the "Client" and "Project" columns of the table, and determines if there are any entries in the table that match both of those criteria. For example, if my table contains "Client_1, Project_2," and you type "Client_1, Project_2" into the Form, it will give you the message "Are you sure you want to update this entry?"
If you select "No", the Form is clearerd, If you select "Yes", I want it to update the entry with the new information that is typed into the Form (There are 4 additional columns that are not part of the search criteria).
Right now, the Form populates whatever cell is selected by the cursor, I want it to select the starting cell for the Form entry as the "Client" column, and the row where "Client_1, Project_2" is entered.
I have pasted my code below and inserted comments where I think the references need to go to select the "Client" cell associated with my search criteria.
Code:
Sub Find_All_Test()
'++++++STILL BROKEN++++++++
'Searches Client and ProjectName columns for existing entries by the same name
'If the entry already exists, it sends message box saying "are you sure you want to update record?"
'If No, it calls resetForm
'If Yes, it updates the record - +++++++Need to find way to select client cell of entry to be updated+++++++
'If the entry does NOT exist, it calls the Form_Fill_DB function to add entry to the bottom of the list
For Each Client_Entry In Range("Client")
If C_Rng = Empty And Client_Entry = TxtClient.Value Then
C_Rng = Client_Entry.Address(0, 0)
ElseIf Client_Entry = TxtClient.Value Then
C_Rng = C_Rng & "," & Client_Entry.Address(0, 0)
End If
Next Client_Entry
If C_Rng = Empty Then
Call Form_Fill_DB
Else
For Each Project In Range(C_Rng):
If Project.Offset(0, 1).Value = TxtProjectName.Value Then
'Either here
If MsgBox("Are you sure you want to update this record?", vbQuestion + vbYesNo) = vbYes Then
'Or here, I want it to select the cell determined to be equal to "TxtProjectName.Value" and update the entry to the following:
ActiveCell = TxtClient.Value
ActiveCell.Offset(0, 1) = TxtProjectName.Value
ActiveCell.Offset(0, 2) = CbxStatus.Value
ActiveCell.Offset(0, 3) = CbxBillable.Value
ActiveCell.Offset(0, 4) = TxtStartDate.Value
ActiveCell.Offset(0, 5) = TxtEndDate.Value
Else
Call resetForm
End If
Call resetForm
Exit For
End If
Next Project
End If
End Sub
Im new to VBA and Im having trouble with a project Im working on.
I've created a "database" within excel that you populate with a Form.
I've got most of the functions working, but Im hung up on one bit.
The logic of the Form entry works like this:
When the Form CommandButton is pressed, it searches the data table that the form populates for criteria in the "Client" and "Project" columns of the table, and determines if there are any entries in the table that match both of those criteria. For example, if my table contains "Client_1, Project_2," and you type "Client_1, Project_2" into the Form, it will give you the message "Are you sure you want to update this entry?"
If you select "No", the Form is clearerd, If you select "Yes", I want it to update the entry with the new information that is typed into the Form (There are 4 additional columns that are not part of the search criteria).
Right now, the Form populates whatever cell is selected by the cursor, I want it to select the starting cell for the Form entry as the "Client" column, and the row where "Client_1, Project_2" is entered.
I have pasted my code below and inserted comments where I think the references need to go to select the "Client" cell associated with my search criteria.
Code:
Sub Find_All_Test()
'++++++STILL BROKEN++++++++
'Searches Client and ProjectName columns for existing entries by the same name
'If the entry already exists, it sends message box saying "are you sure you want to update record?"
'If No, it calls resetForm
'If Yes, it updates the record - +++++++Need to find way to select client cell of entry to be updated+++++++
'If the entry does NOT exist, it calls the Form_Fill_DB function to add entry to the bottom of the list
For Each Client_Entry In Range("Client")
If C_Rng = Empty And Client_Entry = TxtClient.Value Then
C_Rng = Client_Entry.Address(0, 0)
ElseIf Client_Entry = TxtClient.Value Then
C_Rng = C_Rng & "," & Client_Entry.Address(0, 0)
End If
Next Client_Entry
If C_Rng = Empty Then
Call Form_Fill_DB
Else
For Each Project In Range(C_Rng):
If Project.Offset(0, 1).Value = TxtProjectName.Value Then
'Either here
If MsgBox("Are you sure you want to update this record?", vbQuestion + vbYesNo) = vbYes Then
'Or here, I want it to select the cell determined to be equal to "TxtProjectName.Value" and update the entry to the following:
ActiveCell = TxtClient.Value
ActiveCell.Offset(0, 1) = TxtProjectName.Value
ActiveCell.Offset(0, 2) = CbxStatus.Value
ActiveCell.Offset(0, 3) = CbxBillable.Value
ActiveCell.Offset(0, 4) = TxtStartDate.Value
ActiveCell.Offset(0, 5) = TxtEndDate.Value
Else
Call resetForm
End If
Call resetForm
Exit For
End If
Next Project
End If
End Sub
