Home
Monday, October 23, 2017
10:19:12 PM
Users online: 0   You are here >> Home > Programming

Forums | Programming Forums search
Forum FAQ
   
  1  
VBA (excel) - recursive call with 'sub x_change(...)'
BoringName 
13/8/08 2:47:11 PM
Apprentice
Greetings. Am trying to get a program done for work in VBA/excel (don't ask!). I have two listboxes on a sheet (both populated from data on a second sheet via the ListFillRange property).

What I am trying to accomplish, is when an option in one listbox is selected, any option in the other listbox is deselected. Also, depending on the most recent selection, the font and border colour change as well as the text being deleted.

Here are the relevant parts of the code
 
Private Sub listboxAviation_Click()
listboxGeneral.value = ""
Dim choice As String
choice = Left(listboxAviation.value, 1)
If choice = "O" Or choice = "P" Or choice = "Q" Then
greyAcademic (True)
greyTraining (True)
Else
greyAcademic (True)
greyTraining (False)
End If
displayROSO
End Sub

Private Sub listboxGeneral_Click()
listboxAviation.value = ""
Dim choice As String
choice = Left(listboxGeneral.value, 2)
If choice = "Co" Or choice = "Ov" Then
greyAcademic (True)
greyTraining (False)
Else
If choice = "Fu" Then
greyAcademic (False)
greyTraining (True)
Else
greyAcademic (True)
greyTraining (True)
End If
End If
displayROSO
End Sub

Private Function greyAcademic(ByVal blank As Boolean)
Range("cellAcademic").value = ""
If blank Then
Range("cellAcademic").BorderAround ColorIndex:=15, Weight:=xlMedium
Range("cellAcademicLabel").Font.ColorIndex = 15
Else
Range("cellAcademic").BorderAround ColorIndex:=4, Weight:=xlMedium
Range("cellAcademicLabel").Font.ColorIndex = 1
End If
End Function

Private Function greyTraining(ByVal blank As Boolean)
Range("cellTraining").value = ""
If blank Then
Range("cellTraining").BorderAround ColorIndex:=15, Weight:=xlMedium
Range("cellTrainingLabel").Font.ColorIndex = 15
Else
Range("cellTraining").BorderAround ColorIndex:=4, Weight:=xlMedium
Range("cellTrainingLabel").Font.ColorIndex = 1
End If
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("cellROSOoutputStart") Or Target = Range("cellROSOoutputEnd") _
Or Target = Range("cellTrainingLabel") Or Target = Range("cellAcademicLabel") Then
Exit Sub
End If
If Target = Range("cellTraining") Or Target = Range("cellAcademic") Then
If Range("cellTraining").value = 0 Or Range("cellAcademic").value = 0 Then
Exit Sub
End If
End If
Application.EnableEvents = False
displayROSO
Application.EnableEvents = True
End Sub


My problem is as follows:
When a new selection is made (in either listbox), in this example listboxAviation, the procedure listboxAviation_Click() is called. This will execute ok, and will then call the procedure greyAcademic(). Upon the first line execute from greyAcademic (the line Range("cellAcademic").value = ""), it will initiate a call back to listboxAviation_click() and start that procedure over again.

Upon the second call to greyAcademic, the whole procedure will function correctly, with a call to worksheet_change(...) being made at the correct times (basically every line).

I was able to determine that this was happening by inserting a break point at every line in the code. Upon the second time running of the listboxAviation_Click procedure, the call stack looked as follows:

VBAProject.Sheet4.listboxAviation_Click
[<Non-Basic Code>]
VBAProject.Sheet4.greyAcademic
VBAProject.Sheet4.listboxAviation_Click

From what I have been able to ascertain, Non-Basic Code means a reference to the windows API.

Overall, it appears that this loop I have (attempted to) describe is in itself looped 6 times. Is this any coincedence to there being 6 items in each list? And is there a problem with my code, or it is something deeper within the MS implementation of VBA?

Cheers

-----
Is sex dirty? Only when it's done right.
--Woody Allen

pappes 
23/8/08 2:59:32 PM
Titan

I had a quick go at replicating your situation but you instructions are not 100% clear.

I created 2 listboxes and configured the properties to show a range of values (a,b,c) and (d,e,f) using the input range property.

I then assigned a value into the celllink property to synchronise a different cell with the value for for each list box.

I also changed the default name of each listbox so that I could track what was happening.

I assigned a "macro" to each listbox and began copying your code in. This is where you example becomes unclear.

listboxAviation_Click/listboxGeneral_Click - I could not see how to get a "_click" event, mine came through as "_Change"
listboxGeneral.value/listboxAviation.value - I was unable to get the code to compile when I used (listboxname).value I changed this to ActiveSheet.Range("<<celllink>>").Value where <<celllink>> is the addrees of the cell I assigned earlier.

I also added some debug.print statements to I could track code execution using the debug window.

Now whenever I click on any vlaue in the listbox the _change event fires and the value in <<celllink>> is shown in the debug window.
Manully changing the value in <<celllink>> chases the listbox to update but does not cause the _change event to fire so nothing displays in the debug window.


At this point my testcase diverges from your testcase too far to make it worth continuing but it does give you some things to ponder:

Can you use debug.print to track code execution?
Can you switch from the _click event to the _change event?
should you be using <<cellink>> instead of directly modifying the value in the listbox?

Can you give more detailed instructions (or raidshare a testcase) so that we can assist you?

-----
I am petitioning to be the first person to be awarded a Nobel prize for prescience. It doesn't exist yet but mark my words it will.

BoringName 
26/8/08 1:11:21 PM
Apprentice
I'm using excel 2003. This could possibly be the reason why you don't have a '_click' event.

I've tried with the '_change' event on the listboxes. This presented me with the problem of deselecting the other listbox. I was unable at the time to come up with a way to determine which was the listbox the user last chose an option, as both listbox events deselected the other listbox selection. This would then deselect any option from both listboxes.

In my version, I didn't use a the 'linkedcell' property. The .value property returned in essence what the linkedcell does, just without having a cell on the worksheet. The user doesn't have an option to change any value within the listbox, merely to select from one of the options presented. I was tossing up whether to use the linkedcell way to do this, but went with the other way that worked (sort of).

I understand that a working copy of the document would be best, however because of the nature of the program, some parts cannot be released. Rewriting of these parts is not really feasible, as the program does what it needs to (eventually...) and I have other things to do.

-----
Is sex dirty? Only when it's done right.
--Woody Allen

  1  
Forums | Programming