Currently my code is throwing a warning message when I click into an empty cell. I need it to display the error box AFTER I go to the next cell, if the previous one was left empty.
My second issue is my drop down combobox runs off the screen so I can't see the last 2 items on the list, no matter if I select AutoSize property or change the ListRows property. Any help?
PS I know its messy, I didn't make it, only trying to troubleshoot existing code. I'm very new to VBA. Thanks in advance
Code
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This code is entered in the Modules folder when you 'View Code' from the Macros tab in the Ribbon
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Auto_close()
ThisWorkbook.Saved = True
End Sub
Sub Maintenance()
' This macro is a toggle and it's purpose is to prevent/allow the
' autocomplete combobox from being displayed so that Data
' Validation can be maintained if necessary.
gbMaintBeingDone = Not gbMaintBeingDone
End Sub
Public Sub ShowAutocomplete(Target As Range)
Dim strVF As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim strParts() As String
Dim lngIndex As Long
Dim phase_table_arr As Variant
Dim i As Long
Dim colx As Integer, rowx As Integer, row_header As Integer
On Error GoTo errHandler
Set ws = ActiveSheet
If gbMaintBeingDone Then
Exit Sub
End If
Set cboTemp = ws.OLEObjects("TempCombo")
'On Error Resume Next
With cboTemp
' Clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
' The cell contains a data validation list
Application.EnableEvents = False
With cboTemp
' Show the combobox with the list
.Visible = False
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
' Optionally increase the font size
'ActiveSheet.TempCombo.Font.Size = 24
If Not Target.Column = ws.Range("ColumnLast").Column Then
If Left$(Target.Validation.Formula1, 1) <> "=" Then
' The dropdown data is a plain List of values like one,two,three
ActiveSheet.TempCombo.Clear
strParts = Split(Target.Validation.Formula1, ",")
For lngIndex = 0 To UBound(strParts)
ActiveSheet.TempCombo.AddItem strParts(lngIndex)
Next
Else
' The dropdown data comes from a Named Range.
' Get the data validation formula.
strVF = Target.Validation.Formula1
strVF = Right(strVF, Len(strVF) - 1)
.ListFillRange = strVF
End If
.LinkedCell = Target.Address
Else
'Clear listfillrange just in case user selects blank row
.ListFillRange = ""
.LinkedCell = Target.Address
End If
End With
cboTemp.Activate
' Open the drop down list automatically
ActiveSheet.TempCombo.DropDown
If (ActiveSheet.TempCombo.Text = "") Then
MsgBox "Cell is blank, please input/select valid data."
Exit Sub
End If
End If
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
errHandler:
Application.EnableEvents = True
' If it's 1004 there's no data validation in the cell
If Err.Number <> 1004 Then
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowAutocomplete"
End If
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This code is entered in the Objects folder when you 'View Code' from the Active Worksheet tab
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Private Sub TempCombo_Error(ByVal Number As Integer, ByVal Description As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
' Move to next cell if Tab or Enter are pressed
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
End Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
If Application.CutCopyMode Then
'allow copying and pasting on the worksheet
GoTo errHandler
End If
ShowAutocomplete Target
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub