Excel Forum

Code: Select all

Private Sub ComboBox6_Change()
If Range("xIndexEnergy1_Can") = "<--Please click & Select Data-->" Then
        Sheets("INPUT").Range("xEFEnergyM1_Can").Value = 0#
    ElseIf Range("xIndexEnergy1_Can") = "Electricity " Then
        Sheets("INPUT").Range("xEFEnergyM1_Can").Value = 0.5812
    ElseIf Range("xIndexEnergy1_Can") = "Gasoline" Then
        Sheets("INPUT").Range("xEFEnergyM1_Can").Value = 0.689
    ElseIf Range("xIndexEnergy1_Can") = "Natural Gas " Then
        Sheets("INPUT").Range("xEFEnergyM1_Can").Value = 0.0099
    ElseIf Range("xIndexEnergy1_Can") = "Liquefied petroleum gas (LPG)" Then
        Sheets("INPUT").Range("xEFEnergyM1_Can").Value = 0.27
    ElseIf Range("xIndexEnergy1_Can") = "Diesel_Combustion" Then
        Sheets("INPUT").Range("xEFEnergyM1_Can").Value = 2.708
    ElseIf Range("xIndexEnergy1_Can") = "Benzene_Combustion" Then
        Sheets("INPUT").Range("xEFEnergyM1_Can").Value = 2.1896
    ElseIf Range("xIndexEnergy1_Can") = "Bunker Oil" Then
        Sheets("INPUT").Range("xEFEnergyM1_Can").Value = 0.0926
    ElseIf Range("xIndexEnergy1_Can") = "Coking Coal_Combustion" Then
        Sheets("INPUT").Range("xEFEnergyM1_Can").Value = 2.6268
    ElseIf Range("xIndexEnergy1_Can") = "Not Calculate" Then
        Sheets("INPUT").Range("xEFEnergyM1_Can").Value = 0#
  End If
  
    If Range("xIndexEnergy1_Can") = "Custom" Then
    With UserForm26
        .Show
    End With
    End If

End Sub

Code: Select all

Private Sub CommandButton1_AddEF_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("UsEF_Utility")

    'check for a name
    If Trim(Me.TextBox8_Name_Body.Value) = "" Then
        Me.TextBox8_Name_Body.SetFocus
        MsgBox "Please enter a name of raw material or chemical"
    Exit Sub
    End If

    'check for a CF
    If Trim(Me.TextBox3_CF_Body.Value) = "" Then
     Me.TextBox3_CF_Body.SetFocus
     MsgBox "Please enter a emission factor"
    Exit Sub
  
End If

    If Not IsNumeric(TextBox3_CF_Body) Then
        MsgBox "Please enter numeric."
        Me.TextBox3_CF_Body.Text = "0.00"
        Exit Sub
    End If
    
ListBox1_SelectNewRW.AddItem TextBox8_Name_Body

Answer = MsgBox("Do you want save change?", 1 + 32, "Create record")
    
If Answer = 1 Then

 'find first empty row in database
    iRow = ws.Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).Row

'copy the data to the database
    ws.Cells(iRow, 4).Value = Me.TextBox2.Value
    ws.Cells(iRow, 5).Value = Me.TextBox8_Name_Body.Value
    ws.Cells(iRow, 10).Value = Me.TextBox3_CF_Body.Value
    ws.Cells(iRow, 11).Value = Me.ComboBox1.Value
    ws.Cells(iRow, 12).Value = Me.TextBox4_Source_Body.Value
    ws.Cells(iRow, 13).Value = Me.TextBox5_Year_Body.Value
    ws.Cells(iRow, 14).Value = Me.TextBox15_Location_Body.Value
    ws.Cells(iRow, 15).Value = Me.TextBox7_Comment_Body.Value

'clear the data
    Me.TextBox8_Name_Body.Value = ""
    Me.TextBox3_CF_Body.Value = ""
    Me.TextBox4_Source_Body.Value = ""
    Me.TextBox5_Year_Body.Value = ""
    Me.TextBox15_Location_Body.Value = ""
    Me.TextBox7_Comment_Body.Value = ""
    Me.ComboBox1.Value = ""
'Unload Me

'ElseIf Answer = 2 Then
   ' Unload Me
'Exit Sub

End If
End Sub

Private Sub CommandButton4_Clear_Click()

    Me.TextBox8_Name_Body.Value = ""
    Me.TextBox3_CF_Body.Value = ""
    Me.TextBox4_Source_Body.Value = ""
    Me.TextBox5_Year_Body.Value = ""
    Me.TextBox15_Location_Body.Value = ""
    Me.TextBox7_Comment_Body.Value = ""
    Me.ComboBox1.Value = ""
' Unload Me
End Sub

Private Sub CommandButton3_Exit_Click()
    Unload Me
End Sub

Private Sub CommandButton5_Exit_Click()
    Unload Me
End Sub

Private Sub CommandButton6_Select_Click()
With Worksheets("Input")
       .Range("xIndexEnergy1_Can") = UserForm26.ListBox1_SelectNewRW
       .Range("xEFEnergyM1_Can") = UserForm26.TextBox14_SelectCF
       .Range("xUnitEnergyMachine1_Can") = UserForm26.TextBox21
End With
End Sub

Private Sub CommandButton7_Delete_Click()
    Dim lng As Long
     Answer = MsgBox("Are you sure you want to delete data from database?", 4 + 48, "Delete database")
    
    If Answer = 6 Then
     lng = Application.Match(ListBox1_SelectNewRW.Value, Worksheets("UsEF_Utility").Range("E:E"), 0)
     Worksheets("UsEF_Utility").Rows(lng).Delete
     Unload Me
    ElseIf Answer = 7 Then
    End If

End Sub

Private Sub CommandButton8_AddInputSh_Click()
      With Worksheets("Input")
       .Range("xIndexEnergy1_Can") = UserForm26.TextBox8_Name_Body
       .Range("xEFEnergyM1_Can") = UserForm26.TextBox3_CF_Body
       .Range("xUnitEnergyMachine1_Can") = UserForm26.ComboBox1
     End With
      'ListBox1_SelectNewRW.AddItem TextBox8_Name_Body
End Sub


Private Sub ListBox1_SelectNewRW_Click()
With Worksheets("UsEF_Utility")
      TextBox14_SelectCF.Value = Application.VLookup(Me.ListBox1_SelectNewRW, .Range("E15:Q100"), 6, False)
      TextBox12_SelectSource.Value = Application.VLookup(Me.ListBox1_SelectNewRW, .Range("E15:Q100"), 8, False)
      TextBox11_SelectYear.Value = Application.VLookup(Me.ListBox1_SelectNewRW, .Range("E15:Q100"), 9, False)
      TextBox10_SelectLocation.Value = Application.VLookup(Me.ListBox1_SelectNewRW, .Range("E15:Q100"), 10, False)
      TextBox9_SelectComment.Value = Application.VLookup(Me.ListBox1_SelectNewRW, .Range("E15:Q100"), 11, False)
      'ComboBox1.Value = Application.VLookup(Me.ListBox1_SelectNewRW, .Range("E15:Q100"), 7, False)
      TextBox21.Value = Application.VLookup(Me.ListBox1_SelectNewRW, .Range("E15:Q100"), 7, False)
    End With
End Sub



Private Sub TextBox21_Change()

End Sub

Private Sub UserForm_Initialize()
Dim rall As Range
  Dim r As Range
   With Sheets("UsEF_Utility")
      Set rall = .Range("E15", .Range("E" & Rows.Count).End(xlUp))
   End With
   For Each r In rall
   ListBox1_SelectNewRW.AddItem r ' Change lisbox1 to ListBox1
   Next r
End Sub

Code: Select all

Private Sub UserForm_Initialize()
    Dim rall As Range
    Dim r As Range
    With Sheets("UsEF_Utility")
    Set rall = .Range("E15", .Range("E" & Rows.Count).End(xlUp))
    End With
    For Each r In rall
        UserForm27.ListBox1_SelectNewRW.AddItem r ' Change lisbox1 to ListBox1
    Next r
End Sub

Code: Select all

Private Sub UserForm_Initialize()
    Dim rall As Range
    Dim r As Range
    With Sheets("UsEF_Utility")
    Set rall = .Range("E15", .Range("E" & Rows.Count).End(xlUp))
    End With
    For Each r In rall
        If r <> "" Then
            UserForm27.ListBox1_SelectNewRW.AddItem r ' Change lisbox1 to ListBox1
        End If
    Next r
End Sub

Code: Select all

Private Sub UserForm26_Initialize()
    Dim rall As Range
    Dim r As Range
    With Sheets("UsEF_Utility")
    Set rall = .Range("E15", .Range("E" & Rows.Count).End(xlUp))
    End With
    For Each r In rall
        If r <> "" Then
            UserForm26.ListBox1_SelectNewRW.AddItem r ' Change lisbox1 to ListBox1
        End If
    Next r
End Sub