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).
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
