Блог

Я следил за учебным пособием на Youtube для менеджера сотрудников, в нем используется множество функций, которые мне не нужны, поэтому я пытался выбрать нужные ему части. У меня нет опыта работы с VBA или кодированием, поэтому я изо всех сил пытаюсь понять, почему некоторые вещи не будут работать.

На скриншотах есть аннотации предполагаемых результатов.

Чем вы заранее

Пример и аннотации предполагаемого результата

Сообщение об ошибке

Отладочный вид

Вот мои макросы для кнопок и обновления

 Option Explicit

Public EventRow As Long
Public EventCol As Long
Public MapRng As String

Sub Event_SaveNew()

    With Sheet6
    
        'check req fields
        If Sheet3.Range("g3").Value = Empty Or Sheet3.Range("g5").Value = Empty Then
        
            MsgBox ("Event Name and Event Type are required fields for ANY Event")
            Exit Sub
            
        End If
        
        EventRow = .Range("E2000").End(xlUp).Row   1 'first av row
        .Cells(EventRow, 5).Value = Sheet3.Range("b7").Value 'new event id
        
        For EventCol = 6 To 12
        
            .Cells(EventRow, EventCol).Value = Sheet3.Range(.Cells(1, EventCol).Value).Value 'event feild
            
        Next EventCol
        
    End With
    
    With Sheet3
    
        .Range("B2").Value = False               'set new event to false
        .Range("B4").Value = Sheet6.Cells(EventRow, 1).Value 'New event ID
        Event_Refresh
        'reload events
    End With
    
End Sub

Sub Event_Refresh()

    Dim LastEventRow As Long
    Dim LastFiltRow As Long
    
    With Sheet3
    
        'load Events list into events sheet using adv filter and sort
        .Range("f22:m2000").ClearContents        'clear existing events list
        LastEventRow = Sheet6.Range("e2000").End(xlUp).Row   6
        Sheet6.Range ("e4:L" amp; LastEventRow)
        
    End With
    
End Sub

Sub Event_AddNew()
    With Sheet3
    
        .Range("B1").Value = True                'set event load to true
        .Range("b2").Value = True                'set new event to true
        .Range("J7,j5,g5,g3,f11:j17,g7,g8,j3").ClearContents
        .Range("b1").Value = False               'set event load to false
        .Range("G3").Select
        
    End With
    
End Sub

Sub Event_Load()
    Dim SelRow As Long
    
    With Sheet3
    
        SelRow = .Range("b9").Value
        
        If .Range("B3").Value = Empty Then
        
            MsgBox "Please select on an Event to display Event details"
            
            Exit Sub
        End If
        
        .Range("b1").Value = True                'set empload to true
        EventRow = .Range("b3").Value
        
    End With
    
    With Sheet6
    
        For EventCol = 6 To 12
        
            MapRng = Sheet6.Cells(1, EventCol).Value
            .Range(MapRng).Value = Sheet6.Cells(EventRow, EventCol).Value 'add maped values
            
        Next EventCol
        
    End With
    
    With Sheet3
    
        .Range("B2").Value = False               'set new event to false
        .Range("B1").Value = False               'set event load to false
        
    End With
    
End Sub

Sub Event_Delete()

    If MsgBox("Are you sure you want to DELETE this event?", vbYesNo, "Delete Event") = vbNo Then Exit Sub
    
    With Sheet3
    
        If .Range("B3").Value = Empty Then Exit Sub
        
        EventRow = .Range("B3").Value            'event row
        Sheet6.Range(EventRow amp; ":" amp; EventRow).EntireRow.Delete
        Event_Refresh                            'refresh events
        
    End With
    
End Sub

Sub Event_CancelNew()

    With Sheet3
    
        .Range("b2").Value = False
        Sheet3.Range("F22").Select
        
    End With
    
End Sub

  

И вот код для страницы журнала событий

 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MapRng As Range
    Dim FoundMapRng As Range
    Dim CellAdd As String
    Dim SellRow As Long
    
    'exsisting event change update event tab
    If Not Intersect(Target, Range("F3:L17")) Is Nothing And Range("B2").Value = False And Range("B1").Value = False And IsNumeric(Cells(Target.Row, Target.Column   25).Value) = True And Cells(Target.Row, Target.Column   25).Value <> Empty Then
        
        Sheet6.Cells(Range("b3").Value, Cells(Target.Row, Target.Column   25).Value).Value = Target.Value
        
    End If
    
    'update below
    CellAdd = Target.Address
    SellRow = Range("b9").Value
    Set MapRng = Sheet3.Range("EventDataMap")
    Set FoundMapRng = MapRng.Find(CellAdd, , xlValues, xlWhole)
    If Not FoundMapRng Is Nothing Then Cells(SellRow, FoundMapRng.Column).Value = Sheet6.Cells(Range("b3").Value, Cells(Target.Row, Target.Column   25).Value).Value
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    
    'on table selection
    If Not Intersect(Target, Range("F22:M2000")) Is Nothing And Range("f" amp; Target.Row).Value <> Empty Then
    
        Range("b9").Value = Target.Row           'add in selection row
        Range("b4").Value = Range("f" amp; Target.Row).Value 'add event ID
        Event_Load
        
    End If
    
End Sub