Solved

help in VBA

Posted on 2016-09-12
11
48 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 36

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 48

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
Technology Partners: 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 48

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 48

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 48

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 48

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 48

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

631 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