Existing data shuts down VBA

[speedracer]

I could attach this, but I think it is much easier to explain this way, as one would have to srot out all of the other points in the code first, which would take too much time I think.

As simply put as possible, I have some VBA code I am running in which I am instructing to write an "x" as text to a given cell. If the cell is empty, or contains anything other than "x", it will write fine and continue to the next line of code. However, if there is already an "x" in that cell, the entire routine ends as if there were an instruction to go to endsub.

The really funny part, is that it did not behave this way yesterday. It would simply write the "x" as instructed no matter what was already in the given cell, and proceed to the next line of code. To the best my recollection, I didnt change anything in the programing or sheet other than some graphic stuff.

I am completely baffled. This was working great, and I need to get it to my boss (an unofficial project I volunteered for of course) today. In fact, in about two hours.

Does anyone have any possible explanation for this behavior?

Please help!

Thanks

[mdmackillop]

Can't suggest anything. Can you post the code? Don't worry about clearing it out.

[speedracer]

This is the code to my problem, please pardon all of the notes and markers
I put in there. I numbered the lines here for reference. Its lines 101 and 102 I am having issues with. Since it didn't seem to want to work, I inserted a command to clear the contents of the cell in 101, but it doesnt seem to want to do that. Im confused.

I think the problem might be that the existing data in that cell is read by another worksheet that is used to populate the combobox. If the value in the combo box corresponds to the "x" in the given cell, it will not let you delete it.

The current values of relevant items: ComboBox1 = 8

************************************
Private Sub ComboBox1_Change()

' enter an X next to name on ServerTallies sheet next to correct server
' what about when a server name is changed and not used -- the x needs to be erased
' ... maybe store previous ComboBox value in a non-visible place, and check any changed value agains the old value so that
' when a server name is changed, the previous server name has the x deleted
' check to see if the new value of the combo box is the same or different from a previous value, and
' if different, delete the x marker from the previous server
'Check the new name to see if it exists in any of the other combo boxes to avoid duplicates.
1 Dim r, c, r2
2 r = 1
3 c = 1
'datacheck
' Worksheets("datatest").Cells(2, 2).Value = ComboBox1
'check that new selection is not the same as the previous selection
'if the same, then end
'if not the same, then erase the previous x for the previously selected name
'note that when no value is in the combo box, or when name is deleted inside box...
'...no value is assigned to object. This is not a zero value
' look for server name value and save row value as r
' add 1 to r if cell down column 1 is not equal to ComboBox1 value

4 Do While Worksheets("ServerTallies").Cells(r, c).Value <> ComboBox1
' *
5 r = r + 1
6 Loop
7 r2 = r
8 Worksheets("datatest").Cells(1, 1).Value = "YES"
'erase previous server x and insert new server x
9 If Worksheets("datatest").Cells(2, 2).Value <> 1 Then
' *
' Debugging Datacheck for combobox2 value

' *
'*************************************************************
10 If Worksheets("datatest").Cells(2, 2).Value <> ComboBox1 Then
' * *
11 r2 = r
12 r = 1
'find the previous server entry and save row value
13 Do While Worksheets("datatest").Cells(r + 2, 4).Value <>
Worksheets("datatest").Cells(2, 2).Value
' * *
14 r = r + 1
15 Loop
'delete x for previous server
16 Worksheets("datatest").Cells(r + 2, 3).ClearContents
17 GoTo 100
' *
18 End If
'*************************************************************
'store the new value
19 Worksheets("datatest").Cells(2, 2).Value = ComboBox1
20 Else: GoTo 100
21 End If
'store the new value
100 Worksheets("datatest").Cells(2, 2).Value = ComboBox1
' * *
101 Worksheets("datatest").Cells(r2, 3).ClearContents
102 Worksheets("datatest").Cells(r2, 3).Value = "x"

103 End Sub

[mdmackillop]

There is nothing after writing X other than End Sub, What else should happen?

[speedracer]

I tried moving it to the end to see if that would help. It was earlier in the code.

If the cell is already empty when it gets to 101 and is instructed to clearcontents, it wont perform the comand, and the routine ends. 102 does not execute. That is what is weird.

[mdmackillop]

If the two lines are together, line 101 is irrelevant. Why not just
Worksheets("datatest").Cells(r2, 3) = "x"

[mdmackillop]

BTW looping is an inefficient way to find the row number. Find is much better. eg (subject to your data type)
[vba]r = Worksheets("ServerTallies").Columns(1).Find(ComboBox1.Value).Row
r2 = r
[/vba]

[speedracer]

the looping ti[ is a big help. Didnt even know about the find thing.

I think this is complicated by the fact that that cell is being used to populate a combobox. I need to figure out another way of dynamically populating the combo box.

I guess I am not getting this done today now.

SRM

[mdmackillop]

Can you remove sensitive data and post your workbook?

[speedracer]

Yeah sure. There isnt any sensitive data there. I just thought it to be too much time and work for someone to figure it all out. I am sure that my programming is not the most tidy, as I am self-read and taught, and don't do this for a living. I thought it would be fun and challenging, and a god experience. It still is, but I am a little bit stuck.

I am sure if I did it everyday, I would adapt to a much more efficient and tidy style.

But here is the whole thing. I know this would be much easier in Access, but my boss does not use Access and wanted it in Excel.

FloorPlan is a page I want combo boxes to display servers for a restaurant, for each given station in the restaurant, which are classified as station types (front patio-pf, back patio-pb, main dining-m I want the combo box to display the name of the server, as well as an indicator of whether that server has already been selected for a station (I have an x showing next to their name in the combo box), and their 90 day totals for each given station type. So each time you change a combobox, all of the combo boxes must be updated.

The code is a bit of a mes right now. I have been trying all kinds of thing to get this to work. The code for combobox 1 will be copied, and contained references will be modifed for each of the 20 combo boxes.

Server Sort View I have no problems with. Same for Date record as well.

Its the dynamic comboboxes I am having trouble with.

Its too big to attach here though. I uploaded it to my webserver though. You can download it here: http://www.moments-photo.biz/tempdwnld/ and the filename is Server-Matrix.xls.

Let me know what you think. I very much appreciate the help and patience with this novice!

Ciao

Scott

[mdmackillop]

Your basic problem is that you are going into an endless loop.
Changing combo1 causes a refresh of all others which refreshes combo1 and so on.

[speedracer]

I dont see it that way, and it ran before. I have not had any endless loop errors. It did not behave like an endless loop. All of the combo boxes get the data from the same table. When it changes, it refreshed them all, just as it were just one.

So if I select Bill in combo box one, I want a procedure to run that simply puts an x by his name in all the combo boxes and ends. I dont see how that is an endless loop. It has an end, and it worked before quite well.

Also, what you just stated really doesnt have anyhting to do with my question. The issue I stated at the very beginning is why a routine shuts down when it is instructed to insert data into a cell that already has that value in it. It fails to continue to execute the rest of the code.

Anyway, in an effort to reach a solution...I described what I want to do. How would you go about doing it?

[lucas]

I dont see it that way, and it ran before. I have not had any endless loop errors. It did not behave like an endless loop. All of the combo boxes get the data from the same table. When it changes, it refreshed them all, just as it were just one.

So if I select Bill in combo box one, I want a procedure to run that simply puts an x by his name in all the combo boxes and ends. I dont see how that is an endless loop. It has an end, and it worked before quite well.

Also, what you just stated really doesnt have anyhting to do with my question. The issue I stated at the very beginning is why a routine shuts down when it is instructed to insert data into a cell that already has that value in it. It fails to continue to execute the rest of the code.

Anyway, in an effort to reach a solution...I described what I want to do. How would you go about doing it?

Astounding........

[speedracer]

What is that supposed to mean? Im confused.

I wasnt being snotty, I just asked if he could recommend another way to approach it. I don't see the endless loop issue either. I don't get any loop errors. So yes, its all very odd.

My initial question was why the VBA code stops when trying to insert text into a cell that already has that text in it. It just stops. It doesnt execute the code after it.

It still does it too. If I have a sequence of instructions, with the first instruction being to insert an "x" into a cell that already has an x in it, and then have an instruction right after that that turns another unrelated cell green, I have no green cell. The code just stops as if it was ended. No error mesage. Nothing.

[lucas]

I would suggest that it's possible that it's still looping and never gets to the code you mentioned but you don't accept the loop theory.....

[mdmackillop]

Youir code is actually failing on the copy lines. Do not Select the range you want to copy, just do it.
There is a loop going on, but it appears 6 times, not infinite
Try
[vba]Dim i As Long

Private Sub ComboBox1_Change()

i = i + 1
MsgBox i

' enter an X next to name on ServerTallies sheet next to correct server
' what about when a server name is changed and not used -- the x needs to be erased
' ... maybe store previous ComboBox value in a non-visible place, and check any changed value agains the old value so that
' when a server name is changed, the previous server name has the x deleted

' check to see if the new value of the combo box is the same or different from a previous value, and
' if different, delete the x marker from the previous server
' Check the new name to see if it exists in any of the other combo boxes to avoid duplicates.

Dim r, c, r2
r = 1
c = 1
Application.ScreenUpdating = False
' Copy all of the x's to the reference column for comboboxes to free up column c fr editing
Worksheets("datatest").Range("C2:C52").Copy
Worksheets("datatest").Range("E2:E52").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

'datacheck
' Worksheets("datatest").Cells(2, 2).Value = ComboBox1
'check that new selection is not the same as the previous selection
'if the same, then end
'if not the same, then erase the previous x for the previously selected name
'note that when no value is in the combo box, or when name is deleted inside box...
'...no value is assigned to object. This is not a zero value

' look for server name value usng the corresponding server number, and save row value as r
' add 1 to r if cell down column 1 is not equal to ComboBox1 value
Do While Worksheets("ServerTallies").Cells(r, c).Value <> ComboBox1
' *
r = r + 1
Loop
'transfer r to r2 as permanent row value
r2 = r

'erase previous server x and insert new server x, if previous server was same server, then skip past
If Worksheets("datatest").Cells(2, 2).Value <> 1 Then
' *
'*************************************************************
If Worksheets("datatest").Cells(2, 2).Value <> ComboBox1 Then
' * *
r2 = r
r = 1
'find the previous server entry and save row value
Do While Worksheets("datatest").Cells(r + 2, 4).Value <> Worksheets("datatest").Cells(2, 2).Value
' * *
r = r + 1
Loop
'delete x for previous server
Worksheets("datatest").Cells(r + 2, 3).ClearContents
' *
GoTo 100
End If
'*************************************************************

'store the new value
Worksheets("datatest").Cells(2, 2).Value = ComboBox1
Else: GoTo 100
End If
'store the new value
100 Worksheets("datatest").Cells(2, 2).Value = ComboBox1
' * *
' Worksheets("datatest").Cells(r2, 3).ClearContents
Worksheets("datatest").Cells(r2, 3).Value = "x"


'Recopy all data
Worksheets("datatest").Range("C2:C52").Copy
Worksheets("datatest").Range("E2:E52").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.ScreenUpdating = True
End Sub
[/vba]

[speedracer]

That helped. I didnt think it was "still looping" because nothing was frozen up, the sub just ended.

I am starting over basically from scratch on the combo box problem, with all of these things in hand. I think it will be better now.

I will let you know what my final result is...

Thanks for the tips! I always learn something new here...

SRM

[speedracer]

OK, I have restarted from scratch. It seems that anything I want to do with the source data that populates the combobox creates problems.

It is the specific desire of the user to be able to see in the list that is contained in the combobox, some type of indicator next to the names of whether or not that name has already been used.

Can anyone think of some way to do this? Ideally, I would like to have the names that have already been selected to be displayed in bold and red within the combo box. It seems that text formatting does not get passed to the combo box though. I cannot find a way to have one (or several), but not all names displayed in a different format (color, bold...etc)

This is kind of the whole point of the program. I thought I had it figured out, but Excel and VBA doesnt seem to want to let me do it my way.

Thanks for the help

SRM

[speedracer]

I have the comboboxes populated by a range of cells in another worksheet. One column of those cells gets its data from Column E of cells in the current (datatest) worksheet. Whenever I try to change any of the data in Column A by manually deleting or changing it, it will not let me. No error message, just a beep. I tried doing it through VBA, and the code just stops as if it was instructed to end. An example is the cut and paste sequence below which is surrently the only code in the Combobox1 sub.

1 Worksheets("datatest").Range("C2:C52").Copy
2 Worksheets("datatest").Range("E2:E52").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
4 Application.CutCopyMode = False
5 Worksheets("datatest").Cells(1, 1).Value = 1

The data pastes, but the range of cells is still highlighted, and there is no 1 placed in cell 1,1. I did that to see if the later instructions were being carried out. When I step through this, the sub just ends as if lines 4 and 5 didnt even exist.

So it seems that it will not allow me to modify any data that is related to populating the combobox. I cant think of a way around this.

Hmmm