ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

excel ³ø»ùªí³æ¤W¿é¤J¤£¦PªºItem¦p¦ó±a¤J¤£¦Pªº¹Ï¤ù

¦^´_ 20# h99949
µLªk³]©wListÄÝ©Ê
  1. Private Sub Item_List()   '«Ø¥ßComboBox1ªºList
  2.     Dim AR
  3.     Set Sh = Sheets("ÂøµæÁç")
  4.     With Sh
  5.         If .Range("a1").End(xlDown).Row = .Rows.Count Then
  6.             ComboBox1.Clear
  7.         Else
  8.             AR = .Range("a2:a" & .Range("a1").End(xlDown).Row).Value
  9.             If .Range("a1").End(xlDown).Row = 2 Then AR = Array(AR)
  10.             ComboBox1.List = AR
  11.         End If
  12.     End With
  13. End Sub
½Æ»s¥N½X
°l¥[Äæ¦ì¤u®É=30000*10/20/1000*D/C
  1. Option Explicit  'ª«¥ó¼Ò²Õµ{¦¡½X
  2. Public WithEvents Text_A As MSForms.TextBox
  3. Public WithEvents Text_B As MSForms.TextBox
  4. Private Sub Text_A_Change()
  5.     Dim S As Integer
  6.     With Text_A
  7.          With .Parent
  8.             '°l¥[Äæ¦ì¤u®É = 30000 * 10 / 20 / 1000 * D / C
  9.             If IsNumeric(.TextBox3) And IsNumeric(.TextBox4) Then
  10.                
  11.                 S = CLng(30000) * 10 / 20 / 1000 * .TextBox4 / .TextBox3
  12.                 .TextBox27 = Application.WorksheetFunction.Round(S, 2)
  13.             Else
  14.                 .TextBox27 = ""
  15.             End If
  16.         End With
  17.         '******************************************
  18.         S = Val(Replace(UCase(.Name), "TEXTBOX", ""))
  19.         If (IsNumeric(.text) And Val(.text) > 0) Or (.Name = "TextBox2" And Trim(.text) <> "") Then
  20.             Select Case S
  21.                 Case 2 To 4
  22.                      .BackColor = &HE0E0E0
  23.                 Case Else
  24.                     .BackColor = vbYellow
  25.             End Select
  26.         Else
  27.             .BackColor = vbRed
  28.         End If
  29.         If .Parent.Msg Then .Parent.¨¾§b
  30.     End With
  31. End Sub
  32. Private Sub Text_B_Change()   'Á`»ùªº¥[Á`
  33.     Dim i As Integer, S
  34.     With Text_B
  35.         For i = 8 To 27  '****'°l¥[Äæ¦ì¤u®É
  36.             If IsNumeric(.Parent.Controls("TextBox" & i)) Or Trim(.Parent.Controls("TextBox" & i)) = "" Then
  37.                 S = S + Val(.Parent.Controls("TextBox" & i))
  38.             End If
  39.         Next
  40.         .Parent.TextBox7 = IIf(S = 0, "", Int(S))
  41.         If IsNumeric(.text) Or Trim(.text) = "" Then
  42.             .BackColor = &HE0E0E0
  43.         Else
  44.             .BackColor = vbRed
  45.         End If
  46.        If .Parent.Msg Then .Parent.¨¾§b
  47.     End With
  48. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 21# GBKEE

ª©¥D§A¦n,§ÚFOB¬üª÷ªº´«ºâ·Q§ï¦¨=ROUND(Á`»ù/¬üª÷¶×²v/¤ò§Q²v(°l¥[TextBox38),2)
                                ¼Ú¤¸ªº´«ºâ·Q§ï¦¨=ROUND(Á`»ù/¼Ú¤¸¶×²v/¤ò§Q²v(°l¥[TextBox38),2)        
¤U­±ªºµ{¦¡­n¦p¦ó°l¥[¤ò§Q²v(°l¥[TextBox38)³o¦UÄæ¦ì
Private Sub TextBox7_Change()    'Á`»ù
    If Val(Trim(TextBox7)) <> 0 Then
        If Val(Trim(TextBox5)) <> 0 And Val(Trim(TextBox36)) = 0 Then TextBox36 = Application.WorksheetFunction.Round(TextBox7 / TextBox5, 2)
                                                                                '=ROUND(Á`»ù/¬üª÷¶×²v/¤ò§Q²v(°l¥[),2)
        If Val(Trim(TextBox6)) <> 0 And Val(Trim(TextBox37)) = 0 Then TextBox37 = Application.WorksheetFunction.Round(TextBox7 / TextBox6, 2)
                                                                                '=ROUND(Á`»ù/¬üª÷¶×²v/¤ò§Q²v(°l¥[),2)
        If Val(Trim(TextBox36)) <> 0 Then TextBox5 = Application.WorksheetFunction.Round(TextBox7 / TextBox36, 2) 'Á`»ù*¬ü¤¸¶×²v
        If Val(Trim(TextBox37)) <> 0 Then TextBox6 = Application.WorksheetFunction.Round(TextBox7 / TextBox37, 2) 'Á`»ù*¼Ú¤¸¶×²v
    ElseIf Val(Trim(TextBox7)) = 0 Then
        TextBox5 = ""
        TextBox6 = ""
    End If
    If Msg Then ¨¾§b
End Sub
Private Sub TextBox36_Change()
    If Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox36)) <> 0 Then
        TextBox5 = Application.WorksheetFunction.Round(TextBox7 / TextBox36, 2)
        '=ROUND(G2/30,2)
    ElseIf Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox5)) <> 0 Then
        TextBox5 = TextBox7
    End If
End Sub
Private Sub TextBox37_Change()
    If Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox37)) <> 0 Then
        TextBox6 = Application.WorksheetFunction.Round(TextBox7 / TextBox37, 2)
        '=ROUND(G2/40,2)
    ElseIf Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox6)) <> 0 Then
        TextBox6 = TextBox7
    End If
End Sub

¥¼©R¦W.JPG (76.61 KB)

¥¼©R¦W.JPG

TOP

¦^´_ 21# GBKEE


    ªþ¤WÀÉ®×

³ø»ùªí³æ-2.rar (29.48 KB)

TOP

¦^´_ 21# GBKEE

ª©¥D§A¦n,¦b·s¼WITEM®É¥u¯à¥´¼Æ¦r·s¼W,§ï¦¨¤å¦r®É´N¤£¯à·s¼W
³o­n¦p¦ó­×§ï???

TOP

¦^´_ 24# h99949
·s¼WITEM ¦³³W«h¶Ü?
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 25# GBKEE


    Sub ¨¾§b()  '­nµ¹ª«¥óÃþ§O¼Ò²Õ©I¥s,¤£¥i¥Î Private
    Dim A As Variant, i As Integer, Rng As Range, AR(1 To 2)
    CommandButton1.Enabled = False          '¤£¥i·s¼W
    CommandButton2.Enabled = False          '¤£¥i­×§ï
    CommandButton3.Enabled = False          '¤£¥i§R°£
    A = Application.Match(Val(ComboBox1), Application.Transpose(ComboBox1.List), 0)
    If (IsNumeric(A) And ComboBox1.ListIndex = -1) Or ComboBox1.ListIndex > -1 Then
        CommandButton3.Enabled = True       '¥i¥H§R°£
    End If
    For i = 2 To 35
        If i <= 33 Then
            If i <= 7 Then AR(1) = AR(1) & "," & Trim(Controls("TextBox" & i))
            'Ar(1): °O¿ý -> Description,½c,Cuft,FOB-¬üª÷,FOB-¼Ú¤¸,Á`»ù'¦bªí³æªº¸ê®Æ
            If Controls("TextBox" & i).BackColor = vbRed Then
                CommandButton1.Enabled = False      '¤£¥i·s¼W
                CommandButton2.Enabled = False      '¤£¥i­×§ï
                Exit Sub                            'Â÷¶} [¨¾§b]³oµ{¦¡
            End If
        End If
        If IsNumeric(A) Then AR(2) = AR(2) & Trim(Controls("TextBox" & i))  'Ar(2): °O¿ý -> ªí³æªº©Ò¦³¸ê®Æ
    Next
    If IsError(A) And IsNumeric(Trim(ComboBox1)) And InStr(AR(1) & ",", ",,") = 0 Then CommandButton1.Enabled = True ¬O±q³o¸Ì§ï¶Ü?
                    'IsError(A) :Trim(ComboBox1 ¤£¦b ComboBox1.LIST ¤¤, ¥i¥H·s¼W
                    'IsNumeric(Trim(ComboBox1)) µø¼Æ¦r                , ¥i¥H·s¼W
                    'InStr(Ar(1) & ",", ",,") = 0 ->¨S¦³ªÅ¥Õ          , ¥i¥H·s¼W
    If IsNumeric(A) And Msg_Data <> AR(2) Then CommandButton2.Enabled = True '¥i¥H­×§ï
End Sub

TOP

¦^´_ 25# GBKEE


ª©¥D§A¦n,§Ú±NIsNumeric (Àˬd¬O§_¬°¼Æ¦r)§ï IsText³o¼Ë¤£¹ï¶Ü

If IsError(A) And IsText(Trim(ComboBox1)) And InStr(AR(1) & ",", ",,") = 0 Then CommandButton1.Enabled = True

TOP

¦^´_ 27# h99949

¨S¦³IsText ³o¨ç¼Æ
Trim() :²M°£«e«áªºªÅ¥Õ¦r¦ê
Trim(ComboBox1)<>"" -> ¦³¦r¦ê
  1. If IsError(A) And Trim(ComboBox1)<>"" And InStr(AR(1) & ",", ",,") = 0 Then CommandButton1.Enabled = True
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 28# GBKEE

ª©¥D§A¦n,§ÚFOB¬üª÷ªº´«ºâ·Q§ï¦¨=ROUND(Á`»ù/¬üª÷¶×²v/¤ò§Q²v(°l¥[TextBox38),2)
                                ¼Ú¤¸ªº´«ºâ·Q§ï¦¨=ROUND(Á`»ù/¼Ú¤¸¶×²v/¤ò§Q²v(°l¥[TextBox38),2)        
¤U­±µ{¦¡­×§ï¦¨³o¼Ë¹ï¶Ü?
Private Sub TextBox7_Change()    'Á`»ù
    If Val(Trim(TextBox7)) <> 0 Then
        If Val(Trim(TextBox5)) <> 0 And Val(Trim(TextBox36)) = 0 And Val(Trim(TextBox38)) <> 0 Then TextBox36 = Application.WorksheetFunction.Round(TextBox7 / TextBox5 / TextBox38, 2)
        If Val(Trim(TextBox5)) <> 0 And Val(Trim(TextBox36)) = 0 And Val(Trim(TextBox38)) <> 0 Then TextBox38 = Application.WorksheetFunction.Round(TextBox7 / TextBox5 / TextBox38, 2)
                                                                                '=ROUND(Á`»ù/¬ü¤¸¶×²v/¤ò§Q²v,2)
        If Val(Trim(TextBox6)) <> 0 And Val(Trim(TextBox37)) = 0 And Val(Trim(TextBox38)) <> 0 Then TextBox37 = Application.WorksheetFunction.Round(TextBox7 / TextBox6 / TextBox38, 2)
        If Val(Trim(TextBox6)) <> 0 And Val(Trim(TextBox37)) = 0 And Val(Trim(TextBox38)) <> 0 Then TextBox38 = Application.WorksheetFunction.Round(TextBox7 / TextBox6 / TextBox38, 2)
                                                                                '=ROUND(Á`»ù/¼Ú¤¸¶×²v/¤ò§Q²v,2)

        
        
        If Val(Trim(TextBox36)) <> 0 Then TextBox5 = Application.WorksheetFunction.Round(TextBox7 / TextBox36 / TextBox38, 2) 'Á`»ù*¬ü¤¸¶×²v*¤ò§Q²v
        If Val(Trim(TextBox38)) <> 0 Then TextBox5 = Application.WorksheetFunction.Round(TextBox7 / TextBox36 / TextBox38, 2) 'Á`»ù*¬ü¤¸¶×²v*¤ò§Q²v
        
        If Val(Trim(TextBox37)) <> 0 Then TextBox6 = Application.WorksheetFunction.Round(TextBox7 / TextBox37 / TextBox38, 2) 'Á`»ù*¼Ú¤¸¶×²v*¤ò§Q²v
        If Val(Trim(TextBox38)) <> 0 Then TextBox6 = Application.WorksheetFunction.Round(TextBox7 / TextBox37 / TextBox38, 2) 'Á`»ù*¼Ú¤¸¶×²v*¤ò§Q²v
     
    ElseIf Val(Trim(TextBox7)) = 0 Then
        TextBox5 = ""
        TextBox6 = ""
    End If
    If Msg Then ¨¾§b
End Sub
Private Sub TextBox36_Change()
    If Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox36)) <> 0 Then
        TextBox5 = Application.WorksheetFunction.Round(TextBox7 / TextBox36, 2)
        '=ROUND(Á`»ù/¬ü¤¸¶×²v/¤ò§Q²v,2)
    ElseIf Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox5)) <> 0 Then
        TextBox5 = TextBox7
    End If
End Sub
Private Sub TextBox37_Change()
    If Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox37)) <> 0 Then
        TextBox6 = Application.WorksheetFunction.Round(TextBox7 / TextBox37, 2)
        '=ROUND(Á`»ù/¼Ú¤¸¶×²v/¤ò§Q²v,2)
    ElseIf Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox6)) <> 0 Then
        TextBox6 = TextBox7
    End If
End Sub
Private Sub TextBox38_Change()
    If Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox36)) <> 0 And Val(Trim(TextBox38)) <> 0 Then
        TextBox5 = Application.WorksheetFunction.Round(TextBox7 / TextBox36 / TextBox38, 2)
        '=ROUND(Á`»ù/¬ü¤¸¶×²v/¤ò§Q²v,2)
    ElseIf Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox5)) <> 0 Then
        TextBox5 = TextBox7
    End If
    If Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox37)) <> 0 And Val(Trim(TextBox38)) <> 0 Then
        TextBox6 = Application.WorksheetFunction.Round(TextBox7 / TextBox37 / TextBox38, 2)
        '=ROUND(Á`»ù/¼Ú¤¸¶×²v/¤ò§Q²v,2)
    ElseIf Val(Trim(TextBox7)) <> 0 And Val(Trim(TextBox6)) <> 0 Then
        TextBox6 = TextBox7
    End If
End Sub

¥¼©R¦W.JPG (76.61 KB)

¥¼©R¦W.JPG

TOP

        ÀR«ä¦Û¦b : ¶¢¤HµL¼Ö½ì¡A¦£¤HµL¬O«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD