Solved

help in VBA

Posted on 2016-09-12
11
44 Views
Last Modified: 2016-09-13
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
0
Comment
Question by:Prashansa Shrestha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 41794030
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
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41794102
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
 

Author Comment

by:Prashansa Shrestha
ID: 41794812
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Expert Comment

by:Martin Liss
ID: 41794995
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
 

Author Comment

by:Prashansa Shrestha
ID: 41795113
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41795137
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41795252
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
 

Author Comment

by:Prashansa Shrestha
ID: 41795489
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41796509
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
 

Author Comment

by:Prashansa Shrestha
ID: 41796557
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41796591
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question