Delete of cell or range firing combobox event

[Malciberg]

Good day to all

Although a member for some time it is quite a while since I last posted so here goes, again.

I have a workbook comprising over a hundred sheets. Each sheet holds data for one week consisting of:- In row 4 names of employees, anything between 50 - 70 tradesmen, in column A names of current projects.

Essentially then,a grid into which, each week, is entered the hours worked by each trades person on each project/s.

To make data entry easier and less prone to error, there is a ComboBox (Activex) on the sheet, from where the entry name can be selected for which hours are to be entered and the attached code hides all other columns except the one chosen. When data entry is complete for that person there is a simple command button which unhides all columns.

The list fill range is a vertical list of all employees populated by cell references to row 4.

Occasionally it will be necessary to delete names when people leave our employment etc and when this is done, ie a cell in row 4 is cleared or deleted, the ComboBox column hide code fires and all the columns in the grid are hidden. One then has to click the command button to reveal all he columns again. Time consuming after several alterations.

My question is can I and if so how, stop this happening.

You will probably by suspect that I am pretty much an amateur at VBA code and I am sure there is probably a much easier, faster and more efficient way to set up what I have tried to describe above. However if I can just stop the ComboBox event firing every time I delete or change a cell in the names row I will be delighted.

regards to all

Malcolm

 

[AlphaFrog]

Does the combobox code test if the combobox has a selection before hiding columns?

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] ComboBox1_Change()
    [color=darkblue]If[/color] ComboBox1.ListIndex > -1 [color=darkblue]Then[/color] [color=green]'Test if selection[/color]
        [color=green]'Hide columns[/color]
    [color=darkblue]Else[/color]
        [color=green]'Unhide all columns[/color]
        Columns.Hidden = [color=darkblue]False[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[Malciberg]

Hi AlphaFrog

Thank you for the speedy reply. In answer to your question, do you know I am not sure, so here is my combobox code, which you will no doubt appreciate I have ripped of from a post somwhere else without quite understanding what is going on.

Sub ComboBox1_Change()

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
Dim myC As Range
Dim myName As String
If ComboBox1.ListIndex < 0 Then
    MsgBox "Your selection must be from or match the drop down list", vbCritical
End If
myName = ComboBox1.Value
For Each myC In Range("d4:dn4")
    myC.EntireColumn.Hidden = (myC.Value <> myName)
Next myC
    ActiveWindow.SmallScroll ToRight:=-1
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub

Does that help at all?

my regards

malcolm

 

[AlphaFrog]

Replace your old code with this.

It won't hide the columns when you edit the combobox list if there is nothing selected in the combobox at the time of the edit.

[COLOR=darkblue]Sub[/COLOR] ComboBox1_Change()
    [COLOR=darkblue]Dim[/COLOR] myC [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]If[/COLOR] ComboBox1.ListIndex > -1 [COLOR=darkblue]Then[/COLOR]
        Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] myC [COLOR=darkblue]In[/COLOR] Range("D4:DN4")
            myC.EntireColumn.Hidden = myC.Value <> ComboBox1.Value
        [COLOR=darkblue]Next[/COLOR] myC
        Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    [COLOR=darkblue]Else[/COLOR]
        Columns("D:DN").Hidden = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[Malciberg]

AlphaFrog

thank you so much for your time, I have done that, replaced my code with what you suggested but, still when I edit, ie delete or change an entry in row 4, the combobox fires and hides all columns except the last selected one???

regards

malcolm

 

[AlphaFrog]

AlphaFrog

thank you so much for your time, I have done that, replaced my code with what you suggested but, still when I edit, ie delete or change an entry in row 4, the combobox fires and hides all columns except the last selected one???

regards

malcolm

Is the combobox blank when you edit?
Do you have any other code that hides columns?

Try this...

[color=darkblue]Sub[/color] ComboBox1_Change()
    [color=darkblue]Dim[/color] myC [color=darkblue]As[/color] Range
    [color=darkblue]If[/color] ComboBox1.ListIndex > -1 [B]And ComboBox1.Value <> "" [/B][color=darkblue]Then[/color]
        Application.ScreenUpdating = [color=darkblue]False[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] myC [color=darkblue]In[/color] Range("D4:DN4")
            myC.EntireColumn.Hidden = myC.Value <> ComboBox1.Value
        [color=darkblue]Next[/color] myC
        Application.ScreenUpdating = [color=darkblue]True[/color]
    [color=darkblue]Else[/color]
        Columns("D:DN").Hidden = [color=darkblue]False[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[Malciberg]

AlphaFrog

Thanks for reply, am off to bed now, will respond tomorrow. Much obliged for your concern

regards

malcolm

 

[Malciberg]

Alphafrog

Thanks for your help. I am pleased to report all is working OK now. Using your suggested code, cleaner than mine anyway, and changing Style in the combobox Properties to 0-fmStyleDropDownCombo from 2-fmStyleDropDownList so the drop down can be cancelled before editing the employee list, in line with your question.

Many many thanks for your help and assistance, it is much appreciated

regards

Malcolm

 

[AlphaFrog]

You're welcome. Thanks for the feedback.