Why wont it do?

[thorpyuk]

Dim bfr As Long
bfr = ComboBox1.Value
ComboBox1.Value = bfr: Exit Sub

how come this gives me a "cant change property" error!?

The code is inside my combobox change event...

 

[Norie]

Thorpyuk

Why do you want to do this? In fact what is it you actually want to do?

If you don't want the user to be able to pick a certain value in a combobox why include it in the list in the first place?

 

[thorpyuk]

Hi Norie,

Basically because it'd be really complicated code to create to omit the erronious values :D

In other words, the steering, engine and gearbox all work on my car, so i can overlook the fact that the wing mirrors fall off every now and again :>

This seems to have some success:

Public lastvalue

Private Sub ComboBox1_Change()
ComboBox1.Value = lastvalue
End Sub

Private Sub ComboBox1_GotFocus()
lastvalue = ComboBox1.Value
End Sub

I've yet to try this on my actual spreadsheet though! :s

 

[Norie]

It would be complicated to omit one erroneous value?

How exactly are you populating the combobox? Is there no way to identify the rogue item(s) and exclude them?

 

[Stormseed]

Sorry for the diversion but that is one of the funniest thread titles I have come across in these days:

"Why wont it do ?"

 

[thorpyuk]

Ok.... you asked :s P.S - apologies for the awful code :<

Private Sub Worksheet_Activate()
If blnHaltChange Then Exit Sub
seasonfill2
ComboBox1.ListIndex = 0
End Sub

Sub seasonfill2()
blnHaltChange = True
Dim a, z As Long
Set dic = CreateObject("scripting.dictionary")
With Sheets("Project")
    a = .Range("b1", .Range("b" & Rows.Count).End(xlUp)).Offset(, -1).Resize(, 3).Value
End With
For z = 2 To UBound(a, 1)
    If Not dic.exists(a(z, 2)) Then
        ReDim w(1 To 3, 1 To 1)
        For zz = 1 To 3: w(zz, 1) = a(z, zz): Next
        dic.Add a(z, 2), w
    Else
        w = dic(a(z, 2))
        ReDim Preserve w(1 To 3, 1 To UBound(w, 2) + 1)
        For zz = 1 To 3: w(zz, UBound(w, 2)) = a(z, zz): Next
        dic(a(z, 2)) = w
    End If
Next
Sheets("Template").ComboBox2.List = dic.keys
Sheets("Template").ComboBox2.Value = Sheets("Calendar").Range("E3").Value

If Sheets("Template").ComboBox2.ListIndex = -1 Then blnHaltChange = False: Exit Sub
With Sheets("Template").ComboBox1
    '.ColumnCount = 3
    .Column = dic(Sheets("Template").ComboBox2.Value)
End With
blnHaltChange = False
End Sub

Private Sub ComboBox2_Change()
If blnHaltChange Then Exit Sub
If Sheets("Template").ComboBox2.ListIndex = -1 Then Exit Sub
With Sheets("Template").ComboBox1
    .Column = dic(ComboBox2.Value)
    .ListIndex = 0
End With
End Sub

Ok, that code is how my combobox gets populated. Now, this code runs whenever a user selects a value:

Private Sub ComboBox1_Change()
If blnHaltChange Then Exit Sub
'Dim bfr As Long
'bfr = ComboBox1.Value
If blnHaltChange2 Then GoTo 40
If Application.WorksheetFunction.CountIf(Worksheets("Overview").Range("AJ:AJ"), ComboBox1.Value) = 0 Then MsgBox "No sub-tasks currently set up for this project": Exit Sub
blnHaltChange = True
40 If Application.WorksheetFunction.CountIf(Worksheets("Overview").Range("AJ:AJ"), ComboBox1.Value) = 0 Then GoTo 10
Range("A2").Value = Worksheets("Template").ComboBox1.Value
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim MatchVar
MatchVar = Application.Match(Val(ComboBox1.Value), Worksheets("Project").Range("A:A"), 0)
If Range("U1").Value <> "Stores" Then
    Range("B6").Value = "Start date"
Else
    Range("J6").Value = Worksheets("Project").Cells(MatchVar, "AX")
End If
Sheets("Template").Range("A78:L87").ClearContents
    Set Sh = Worksheets("Adhoc")
    LR = Sh.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Sh.Range("A2:A" & LR)
    With rng
        Set Cell = .Find(What:=ComboBox1.Value, After:=.Cells(1, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)
        If Not Cell Is Nothing Then
            FirstAddr = Cell.Address
            Do
                With Sheets("Template").Range("A90").End(xlUp).Offset(1, 0)
                    .Value = Cell.EntireRow.Cells(1, 7).Value
                    .Offset(0, 1).Value = Cell.EntireRow.Cells(1, 9).Value
                    If Cell.EntireRow.Cells(1, 13).Value = "Y" Then .Offset(0, 2).Value = Cell.EntireRow.Cells(1, 11).Value: .Offset(0, 3).Value = Cell.EntireRow.Cells(1, 12).Value
                End With
            Set Cell = .FindNext(Cell)
            Loop While Not Cell Is Nothing And Cell.Address <> FirstAddr
        End If
    End With
Set rng = Nothing
Set Cell = Nothing

Application.EnableEvents = True
Columns("AF:AG").ClearContents
Dim rngToCopy As Range
With Sheets("Overview")
    .AutoFilterMode = False
    .Range("Aj:AJ").AutoFilter field:=1, Criteria1:=Range("a2").Value
    With .AutoFilter.Range
        On Error Resume Next
        Set rngToCopy = .Offset(1).Resize(.Rows.Count - 1).EntireRow.Resize(, 1)
        On Error GoTo 0
        If blnHaltChange2 Then GoTo 47
        If rngToCopy Is Nothing Then MsgBox "No tasks for given project id!...": Exit Sub
47      If rngToCopy Is Nothing Then blnHaltChange3 = True: Exit Sub
    End With
    rngToCopy.Copy Destination:=Sheets("Template").Range("AF1")
    Set rngToCopy = .AutoFilter.Range.Offset(1, 38)
    rngToCopy.Copy Destination:=Sheets("Template").Range("AG1")
    .AutoFilterMode = False
End With
blnHaltChange = False
Application.ScreenUpdating = True
Exit Sub
10
blnHaltChange3 = True
blnHaltChange = False
Application.ScreenUpdating = True
End Sub

:>

 

[thorpyuk]

Sorry for the diversion but that is one of the funniest thread titles I have come across in these days:

"Why wont it do ?"

hehe,

It's as opposed to 'what have i done?!?!?!' :D

 

[gecs]

If you don't want the user to leave the control until setting it to an admissible value, you may use the Exit event to test for the condition, display the message, set the control's value back to it's initial value and set the Cancel argument to True (which will keep the focus on the control). You may also use the DropDown method of the ComboBox, as an "invitation" to the user to chose another value.

 

[Norie]

What are you actually trying to populate your combobox(s) with?

Are they meant to be cascading?

That is, user picks value from first combobox, only items relevant to the value picked appear in 2nd combobox, user picks from 2nd etc

Can you identify the rogue value(s) in anyway?

And why are you using the chnage event to populate a combobox?

I think we need some explanation in words what the code is meant to do and some sample data wouldn't go amiss either.

PS I'm struggling to follow the flow of the last sub, mainly because of the use of GoTo and On Error Resume Next.

 

[RoryA]

I seriously can't believe you used a GoTo to skip one line!!!

 

[thorpyuk]

I seriously can't believe you used a GoTo to skip one line!!!

Hehe,

only i could have come up with some code as ugly as that lot