Combobox: first click doesn't show value; reclick doesn't run code

Alli

New Member
Joined
May 25, 2010
Messages
25
Hello
I have two separate combobox problems.
1. The first combobox on my form (CmdDiscipline) chooses a 'discipline' e.g. geomorphology, water quality: When i first select a value from it, it doesn't show the value selected (but runs the code, as it moves to the required cell). If I repeat the click the selected value shows.

2. The next combobox (chSite) chooses a site along a river: It works the fine first time around: I select a value, the code runs (it moves to the required cell). But, If I then change the value in the first combobox, and then select the SAME value from the second combobox (i.e. same site), the code doesn't run (it doesn't move to the required cell, but stays where it was after selecting a new disicipline).
Code:
Option Explicit
Private Sub UserForm_Activate()
With Me
  .StartUpPosition = 0
  .Top = 20
  .Left = 15
End With
'Worksheets("Testing").Range("A1").Activate
End Sub
'
Private Sub CmdDiscipline_Change()
Dim numDisciplines As Integer, J As Integer
'
'When I first open the form, and select something from this combobox, the selected value
'doesn't show. I have to select it again, to get the value to show.
'
CmdDiscipline.HideSelection = False
numDisciplines = Worksheets("1 Flow Disc Site Scenario Names").Range("F53")
For J = 1 To numDisciplines
    If CmdDiscipline.Value = Worksheets("1 Flow Disc Site Scenario Names").Range("F42").Offset(J - 1, 0) Then
      Worksheets("Testing").Range("A10").Offset((J - 1) * 10, 0) = CmdDiscipline.Value
      Worksheets("Testing").Range("A10").Offset((J - 1) * 10, 0).Activate
      If J = 1 Then
        ActiveWindow.ScrollRow = J
      ElseIf J = 2 Then
        ActiveWindow.ScrollRow = J * 2
      ElseIf J <= 4 Then
        ActiveWindow.ScrollRow = J * 7
      ElseIf J < 7 Then
        ActiveWindow.ScrollRow = J * 8
      ElseIf J < 10 Then
        ActiveWindow.ScrollRow = J * 9
      End If
    End If
Next J
TxtFromList.Value = ""
End Sub
'
Private Sub chSite_Change()
' If I 'select' the SAME site, the code doesn't happen (i.e. doesn't move to the required cell)
'
Dim numSites As Integer, J As Integer, rw As Integer
CmdDiscipline.HideSelection = False
'
numSites = Worksheets("1 Flow Disc Site Scenario Names").Range("J53")
rw = ActiveCell.Row
For J = 1 To numSites
    If chSite.Value = Worksheets("1 Flow Disc Site Scenario Names").Range("J42").Offset(J - 1, 0) Then
      Worksheets("Testing").Range("B8").Offset(0, (J - 1)) = chSite.Value
      Worksheets("Testing").Range("B8").Offset(rw - 8, (J - 1)).Activate
    End If
Next J
End Sub
'
Private Sub chSite_Click()
'This code doesn't ever seem to happen (added to try to make it moved to correct cell)
Dim numSites As Integer, J As Integer, rw As Integer
CmdDiscipline.HideSelection = False
'
numSites = Worksheets("1 Flow Disc Site Scenario Names").Range("J53")
rw = ActiveCell.Row
For J = 1 To numSites
    If chSite.Value = Worksheets("1 Flow Disc Site Scenario Names").Range("J42").Offset(J - 1, 0) Then
      Worksheets("Testing").Range("B8").Offset(0, (J - 1)) = chSite.Value
      Worksheets("Testing").Range("B8").Offset(rw - 8, (J - 1)).Activate
    End If
Next J
End Sub
'
Private Sub CmdAdd_Click()
Dim J As Integer, I As Integer, newRow As Integer
'
    J = 1 + chSite.ListIndex
    I = 1 + CmdDiscipline.ListIndex
    newRow = Worksheets("Testing").Range("B111").Offset(I - 1, J - 1).Value
    If txtNewInd.Text = "" Then
      Worksheets("Testing").Range("B10").Offset(((I - 1) * 10) + newRow, J - 1).Value = IndList.Value
    Else
      Worksheets("Testing").Range("B10").Offset(((I - 1) * 10) + newRow, J - 1).Value = txtNewInd.Text
      txtNewInd.Text = ""
    End If
End Sub

'''

Private Sub CmdClose_Click()
 ActiveWindow.ScrollRow = 1
 Unload Me
End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
You're unlikely to get many responses because for problem 1 it's difficult to reproduce the error. We'd have to do this, because at first sight, it should show the value straight away. Perhaps a glitch to do with memory? Try a userform.repaint, perhaps with Do Events. Otherwise it might be something specific to your userform or combobox which I don't have access to.

Otherwise, for us to try and setup your userform to do this, the code keeps breaking down because it refers to:

  • cells and areas on sheets which have unknown values
  • other controls on the user form, but I don't what they are, nor what they might contain
  • sheets which don't exist

So it's quite hard work to just try to reproduce the problem behaviour.

Perhaps a workbook posted on the web somewhere (skydrive, box.net perhaps). This would answer lots of questions, like what version of excel you're using and a myriad of other questions.

Regarding problem 2, the chsite combobox doesn't change when you select the same site again (so the change event doesn't fire), nor does the click event fire when you select the same item in a combox - don't ask me why not. It will, however, fire when the combo box is changed! So yes, no code runs.
So you should include, when either of the comboxes changes, code which looks at both of comboxes' values. Write this code in a separate sub, so that both comboxes use the same code by calling it.
Do you need to update the active cell while the user selecting different disciplines and sites? Perhaps you do, so that they can see information at the same time - fine, but if not, don't bother having any code in the combobox change/click events, and let the Add button's code do all the processing.
 

Alli

New Member
Joined
May 25, 2010
Messages
25
Dear p45cal
Thanks very much for your response and hints. I will try them out later today and let you know what transpires.
I'll post the workbook if I can't solve anything with your suggestions.
Ta again.
 

Alli

New Member
Joined
May 25, 2010
Messages
25
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CUsers%5CUser%5CAppData%5CLocal%5CTemp%5Cmsohtml1%5C02%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> "Regarding problem 2, the chsite combobox doesn't change when you select the same site again (so the change event doesn't fire), nor does the click event fire when you select the same item in a combox - don't ask me why not."
Do you mean that this is normal behaviour for comboboxes? If so, ok, useful to know!

"It will, however, fire when the combo box is changed! So yes, no code runs. So you should include, when either of the comboxes changes, code which looks at both of comboxes' values. Write this code in a separate sub, so that both comboxes use the same code by calling it."
Thanks, have done so, which solves that problem.

"Do you need to update the active cell while the user selecting different disciplines and sites?"
Yes, otherwise the user can't see what has already been added, but the issue is solved with the separate code as you suggested.

I have not solved the problem of the initial click in the first combobox not showing the selected value... Will get back to that.
Meantime, thanks for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,129,810
Messages
5,638,484
Members
417,027
Latest member
wlknspc7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top