help in VBA

I have designed a userform where the users enter the data. I need to generate a table and fill these data. In the attached worksheet, 'Configure Diesel power plant' you will see the userform and also see that the number of table generated is dependent on the value of number of diesel generator. the thing that is not working is, only the table is being created but the data is not able to be uploaded on the table. can any of you guys please help me.?
Prashansa_help.xlsm
Prashansa ShresthaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
The OK button only copies something from the template.
If you need to transfer data from the form to the sheet, you have to add code in the OK button code.
You already have some code commented out in the Transfer Information section. Adjust it slightly so it lines up correctly in the table, and put all the code underneath the current code (but within the "With ActiveSheet" block)
0
Martin LissOlder than dirtCommented:
Lets assume that you just want to create one generator. Please tell me the cells where each value from the userform should be placed. For example, should the 123 Nominal Power value from the userform be placed in B11? If not then where? I need to know where each field from the userform goes.

Where does the data go?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Prashansa ShresthaAuthor Commented:
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
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Martin LissOlder than dirtCommented:
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

Open in new window

1
Prashansa ShresthaAuthor Commented:
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 :)
0
Martin LissOlder than dirtCommented:
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

Open in new window

0
Martin LissOlder than dirtCommented:
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

Open in new window

0
Prashansa ShresthaAuthor Commented:
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.
0
Martin LissOlder than dirtCommented:
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

Open in new window

1
Prashansa ShresthaAuthor Commented:
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.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Expand my profile’s “Full Biography” and you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.