SHARE
TWEET

My first Excel script

cyecize Apr 13th, 2019 (edited) 92 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Private Sub Worksheet_Activate()
  2.  
  3. End Sub
  4.  
  5. Private Sub Worksheet_Change(ByVal Target As Range)
  6.     Dim Oldvalue As String
  7.     Dim Newvalue As String
  8.  
  9.     'On Error GoTo Exitsub
  10.    If Target.Column = "5" Then
  11.    
  12.         If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
  13.             GoTo Exitsub
  14.         Else: If Target.Value = "" Then GoTo Exitsub Else
  15.             Application.EnableEvents = False
  16.             Newvalue = Target.Value
  17.             Application.Undo
  18.             Oldvalue = Target.Value
  19.             If Oldvalue = "" Then
  20.                 Target.Value = Newvalue
  21.             Else
  22.                 Target.Value = Oldvalue & ", " & Newvalue
  23.         End If
  24.        
  25.         MsgBox (Target.Value)
  26.         Debug.Print Target.Value
  27.        
  28.         'Suzdavane na spisuk s ceni
  29.        Dim dict As Scripting.Dictionary
  30.         Dim total
  31.        
  32.         Set dict = New Scripting.Dictionary
  33.         total = 0
  34.        
  35.         'Populvane na spisuk s ceni
  36.        dict.Add Key:="Чипс", Item:=2.8
  37.         dict.Add Key:="Хляб", Item:=1.4
  38.         dict.Add Key:="Шоколад", Item:=2.4
  39.         dict.Add Key:="Безалкохолни напитки", Item:=1.8
  40.        
  41.         Dim selectedItems() As String
  42.         selectedItems = Split(Target.Value, ",")
  43.        
  44.         For Each element In selectedItems
  45.             If dict.Exists(Trim(element)) Then
  46.                 total = total + dict.Item(Trim(element))
  47.             End If
  48.            
  49.         Next element
  50.        
  51.         Dim PRODUCT_PRICE_COL_NAME
  52.         PRODUCT_PRICE_COL_NAME = "F"
  53.        
  54.         Dim prodPriceCol
  55.         prodPriceCol = PRODUCT_PRICE_COL_NAME + CStr(Target.Row)
  56.         MsgBox (prodPriceCol)
  57.        
  58.         Range(prodPriceCol).Value = total
  59.        
  60.         MsgBox (total)
  61.        
  62.     End If
  63. End If
  64.  
  65. Application.EnableEvents = True
  66. Exitsub:
  67. Application.EnableEvents = True
  68. End Sub
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top