help in VBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

What I require is:

Nominal power: B11
Power Factor: D11
%of load case I: B12
%of load case II: C12
%of load case III: D12
Fuel Consumption case I: B13
Fuel Consumption case II: C13
Fuel Consumption case III: D13

Replace your OKButton1_Click code with this:

Private Sub OKButton1_Click()
Dim lngNextRow As Long
Dim lngRow As Long

With ActiveSheet
    lngNextRow = .Range("A1048576").End(xlUp).Row + 1
    If lngNextRow = 9 Then
        lngNextRow = 10
    End If
    Sheets("Template").Range("A1:F4").Copy Destination:=.Range("A" & lngNextRow & ":F" & lngNextRow)
    ' Calculate the "Diesel" number
    .Cells(lngNextRow, "A") = "Diesel " & (lngNextRow - 6) / 4
    .Cells(lngNextRow + 1, "B") = Nominalpowertextbox.Value
    .Cells(lngNextRow + 1, "D") = Powerfactorcombobox.Value
    .Cells(lngNextRow + 2, "B") = CaseIcombobox.Value
    .Cells(lngNextRow + 2, "C") = CaseIIcombobox.Value
    .Cells(lngNextRow + 2, "D") = CaseIIIcombobox.Value
    .Cells(lngNextRow + 3, "B") = Fuel1textbox.Value
    .Cells(lngNextRow + 3, "C") = Fuel2textbox.Value
    .Cells(lngNextRow + 3, "D") = Fuel3textbox.Value
    If lngNextRow > 10 Then
        ' Reformat the upper border of the new top row
        ReformatBorder lngNextRow
    End If
        
End With
    
'To close the userform window
Unload Me

End Sub

ASKER

Thank you so much Marty.
It is exactly what i wanted.
But I see that, when I use the form second time, the data along with the table adds to the previous one. I want to reset everything when it's a new user. May be it's a very minute thing to do but since I don't have much idea about VBA, it's kinda difficult for me.

Looking forward to hearing soon.
Thank you once again :)

Since the OK button on the userform is clicked multiple times I can't think of a way to decide when the data is old and when it's new. The best I can suggest is to add a new macro and call that macro from a separate button or assign a hotkey to it. This should be added to sheet2's code.

Sub ClearOlddata()

    With Sheets("Configure Diesel Power Plant")
        If vbNo = MsgBox("Clear old Diesel Power Plant data?", vbYesNo) Then
            Exit Sub
        End If
            
        .Rows("9:" & .Rows.Count).Delete
    End With

End Sub

I realized that there's a simple way to delete the previous data that requires neither the addition of a new button nor the need for the ClearOlddata macro. Just add lines 7 to 9.

Private Sub CommandButton1_Click()

Dim intGenerators As Integer

For intGenerators = 1 To Sheets("Configure Diesel Power Plant").Range("B8")
    Data_Diesel_Engine.Caption = "Diesel Power Plant " & intGenerators
    With ActiveSheet
        .Rows("9:" & .Rows.Count).Delete
    End With
    Data_Diesel_Engine.Show vbModal
    If gbCancelled Then
        gbCancelled = False
        If vbYes = MsgBox("Are you sure you want to quit adding generators", vbYesNo) Then
            Exit Sub
        End If
    End If
Next

End Sub

ASKER

Dear Marty,

This code only shows me the data for the last generator. What I want is:
If an user selects 3 diesel generator, I want all the data for 3 generators. And when another user uses the form, I want the table and data to be rest. But what is happening now is only the data for 3rd generator is being shown. The data for first 2 generator is deleted. It's being reset for one user itself.

Sorry about that.

Private Sub CommandButton1_Click()

Dim intGenerators As Integer
With ActiveSheet
    .Rows("9:" & .Rows.Count).Delete
End With

For intGenerators = 1 To Sheets("Configure Diesel Power Plant").Range("B8")
    Data_Diesel_Engine.Caption = "Diesel Power Plant " & intGenerators
    Data_Diesel_Engine.Show vbModal
    If gbCancelled Then
        gbCancelled = False
        If vbYes = MsgBox("Are you sure you want to quit adding generators", vbYesNo) Then
            Exit Sub
        End If
    End If
Next

End Sub

ASKER

Dear Marty,

This is what I wanted. Thank you so much. You have no idea how much you have helped me.
Once again I am very grateful.