help with syntax error, subscript out of range, data transfer and checkbox control

[xljgd]

Hello All

As you may have already guessed i am new to VBA coding.

Below is a code were i am attempting to enter data in a user forma and copy it on a worksheet called InvDet.

After i copy the lines i need to copy the lines into another excel sheet that is external to log this data in a invoice register.

Also on my 3 work sheet i have data that the user needs to interact with and categorize into any of the 3 categories. a data line can only belong to one category.

i then have to sum it based on the category selected. i have no clue how to do this using vba code.

The file is attached and below is the code that is giving me errors.

Private Sub cmdTran_Click()
Dim ws As Worksheet
Set ws = Worksheets("InvDet")
Worksheets("InvDet").Activate


Dim lrCD As Long
lrCD = Sheets(“InvDet”).Range(“A” & Rows.Count).End(xlUp).Row (error on this line) (subscript out of range, run time error 9)
Sheets(“InvDet”).Cells(lrCD + 1, “A”).Value = txtInv.Text
Sheets(“InvDet”).Cells(lrCD + 1, “B”).Value = txtClient.Text
Sheets(“InvDet”).Cells(lrCD + 1, “C”).Value = txtPoAdd.Text
Sheets(“InvDet”).Cells(lrCD + 1, “D”).Value = txtAdd.Text
Sheets(“CustomerDetails”).Cells(lrCD + 1, “D”).Columns.AutoFit
End Sub

your help and time in reviewing this post is appreciated.

 

[Hui]

Try retyping all the " characters
also two of the Txt box names were wrong

Try this code:

Private Sub cmdTran_Click()
Dim ws As Worksheet
Set ws = Worksheets("InvDet")
Worksheets("InvDet").Activate


Dim lrCD As Long
lrCD = Sheets("InvDet").Range("A" & Rows.Count).End(xlUp).Row
Sheets("InvDet").Cells(lrCD + 1, "A").Value = txtInv.Text
Sheets("InvDet").Cells(lrCD + 1, "B").Value = txtClient.Text
Sheets("InvDet").Cells(lrCD + 1, "C").Value = txtAdd.Text
Sheets("InvDet").Cells(lrCD + 1, "D").Value = txtPo.Text
Sheets("CustomerDetails").Cells(lrCD + 1, "D").Columns.AutoFit
End Sub

 

[xljgd]

Thanks Hui,

It worked great. i had mis typed a sheet name and a txt field . Thanks for pointing it out.

The next problem i have is selecting multiple checkboxes based on 2 conditions.

In the data the user selects one check box and based on the number of checkboxes selected the data should be summed for both Task and Type of po either Material type , expense or Travel.

I can use a pivot but the data will gets imported on this worksheet without this check box and the user needs to interact with the data categorize and than sum it.

Not sure which route to take .

 

[Belleke]

For your first question, try to avoid activate (or select), it slows down the code and is almost never needed.
try this
It submits the values and empties the textboxes afther submitting.

Private Sub cmdTran_Click()
Dim lrCD As Long, Ctrl As Control
With Sheets("InvDet")
lrCD = .Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
.Cells(lrCD, 1).Resize(, 4).Value = Array(txtInv.Text, txtClient.Text, txtAdd.Text, txtPo.Text)
End With
Sheets("CustomerDetails").Cells(lrCD + 1, 4).Columns.AutoFit
For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
    Next Ctrl
End Sub

 

[Belleke]

For your second question , I am not sure what you mean.
See attached if this is what you want.
I've change the checkboxes with option buttons.
I grouped them 3 in a row (Material, Travel Expense)
I linked them to their cell
Changed the font color to white
And added formula in E8,F8 and G8

 

[xljgd]

Thanks for your reply. sorry i did not explain myself very well. your formula includes count that is awesome.

i want the values to sum based on Task no and sub total based on the category based on Material Travel and Expense. When the user interacts with the radio button the totals needs to change based on the user input.

I have updated the sheet and attached it.

Appreciate your time in reviewing this request.

 

[xljgd]

For your first question, try to avoid activate (or select), it slows down the code and is almost never needed.
try this
It submits the values and empties the textboxes afther submitting.

Private Sub cmdTran_Click()
Dim lrCD As Long, Ctrl As Control
With Sheets("InvDet")
lrCD = .Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
.Cells(lrCD, 1).Resize(, 4).Value = Array(txtInv.Text, txtClient.Text, txtAdd.Text, txtPo.Text)
End With
Sheets("CustomerDetails").Cells(lrCD + 1, 4).Columns.AutoFit
For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
    Next Ctrl
End Sub

Thank you for the code it worked like a charm. To include more values from text boxes i would define the no. say Resize (,5).Value and include one more item in my array.
worked out great.

 

[xljgd]

For your first question, try to avoid activate (or select), it slows down the code and is almost never needed.
try this
It submits the values and empties the textboxes afther submitting.

Private Sub cmdTran_Click()
Dim lrCD As Long, Ctrl As Control
With Sheets("InvDet")
lrCD = .Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
.Cells(lrCD, 1).Resize(, 4).Value = Array(txtInv.Text, txtClient.Text, txtAdd.Text, txtPo.Text)
End With
Sheets("CustomerDetails").Cells(lrCD + 1, 4).Columns.AutoFit
For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
    Next Ctrl
End Sub

I have this code working but now I want the user to able to go the next and previous lines of record. once the user enters the values and hits the save button all values are filled in the worksheets were it needs to go to and than the data on the user form clears out. if the user made a single mistake they need to retype all the entries in the user form.

so I have included a previous and next button with the following code.

I am not sure how to reference sheet11 which has the invoice data that the userform saves to. (select method of Range class failed) run error "1004")

Private Sub cmdNextData_Click()
Sheet11.Range("A2").Select
activatecell.End(xlDown).Select
lastrow = ActiveCell.Row

currentrow = currentrow + 1
If currentrow = lastrow + 1 Then
currentrow = lastrow
MsgBox "You have reached the last row!"
End If

txtInv.text = Cells(currentrow, 1).Value
txtProj.text = Cells(currentrow, 2).Value
txtAdd.text = Cells(currentrow, 3).Value
txtPO.text = Cells(currentrow, 4).Value
txtDate.text = Cells(currentrow, 5).Value

End Sub


Private Sub CmdPreviousData_Click()
currentrow = currentrow - 1
If currentrow > 1 Then
txtInv.text = Cells(currentrow, 1).Value
txtProj.text = Cells(currentrow, 2).Value
txtAdd.text = Cells(currentrow, 3).Value
txtPO.text = Cells(currentrow, 4).Value
txtDate.text = Cells(currentrow, 5).Value

ElseIf currentrow = 1 Then
MsgBox "This is your first record!"
currentrow = currentrow + 1
End If

End Sub[code\]

 

[Belleke]

Does this help?
See attached

 

[xljgd]

Thanks a ton. That was more that I could ever imagine like the search button etc.


the last task I have is to pick pieces of information and create a running log.

Each invoice value is broken into various costs that needs to be logged per invoice and the details to be transferred to an excel outside on the network.

once the invoice cost is finalized I would have to pick u values from worksheet A ,worksheet B and than log it into a different workbook. once this template is used for different invoices the values will get added line by line in that workbook.


can you kindly guide me through this one. file attached example in sheet 3.