Solved

help in VBA

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

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
 
LVL 45

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 45

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 45

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 45

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 45

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now