Solved: Problem Moving Code from Module to UserForm

[Kicker]

Originally, I created an application workbook at home using Excel 2003 on a Windows XP computer. It worked great when I took it to the office using Excel 2003 on a Windows NT 2002. However, it blew-up when I added a userform in the office and called some of the already working subs from one of the modules. I thought it might be a compatibility problem. Now I am not sure...

Is there a documented problem in this area?

Unfortunately, I cannot post a coy of the workbook because it is company sensitive and contains a lot of personal information.

[lucas]

Thanks for noticing....
Probably not a compatability problem......blew up???? Seriously what really happened...what was the error, etc.

[Kicker]

Sorry about sounding frustrated.

It just froze up on me. It appeared to be in a loop of some sort because I couldn't move the curser except in very very slow jerky motion. Could not get to the windows status bar to open the task manager. Finally opened task manager before I ran the routine and could barely get to it using alt-tab. In the task manager, it took 30 or 40 seconds before it would highlight Excel (which was identified as running) and then another 30 seconds to get it to stop. Once I had to unplug the computer. Never did get an error message.

I have written dozens of routines at home and taken them to the office. Everyone of the worked fine.

When called as a macro, this one works perfectly. When called from a user form or from another macro, it just throws up. Leads me to think. I think I will try to put a command button on the worksheet itself and see what happens.

[Kicker]

Well....it worked when I put a command button on the Exel worksheet.

I am going to continue this way and hopefully can get it to work through a user form.

[lucas]

Would have to have more info on the macro to help you any more....hope you get it worked out....if not post back here.

[Kicker]

OK. I stripped most everything out of the file except what is needed for the sub to run. Changed the names to protect the innocent.

CAUTION....CAUTION....This WILL freeze your computer. Suggest you open it up and read all the macros first.

There are 3 macros.

From the Macros/run menu.
1. Show menu opens a user form
2. TeamSC runs
3. TeamSOS runs

On the Teams worksheet, the command button works to run the macros

On the userform, it goes wacky.

[mdmackillop]

Send Keys is not a good method of coding unless there is no alternative. In this case it is not changing the Active cell as you intend, so this is looping endlessly. I added in tmp for debugging
[vba]
While Not ActiveCell.Value = ""
Dim tmp
tmp = ActiveCell.Address
SendKeys "{DOWN}", True
Wend
[/vba]
Try [VBA]While Not ActiveCell.Value = ""
ActiveCell.Offset(1).Activate
Wend[/VBA]

[lucas]

Not sure what is going on but it's got something to do with the lines in red. If you comment them out for the first button it goes ok.
[vba]Sub team_SOS()
Dim r As Long
Dim rr As Long
Dim n As Long

Sheets("SecResources").Select
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
For n = 1 To 3
Sheets("SecResources").Select
Selection.AutoFilter Field:=3, Criteria1:="SOS"
Select Case n
Case Is = 1
Selection.AutoFilter Field:=2, Criteria1:="=D?", Operator:=xlAnd
Case Is = 2
Selection.AutoFilter Field:=2, Criteria1:="=S?", Operator:=xlAnd
Case Is = 3
Selection.AutoFilter Field:=2, Criteria1:="=G?", Operator:=xlAnd
End Select
Range("A1").Select
SendKeys "{DOWN}", True
r = ActiveCell.Row
While Not ActiveCell.Value = ""
SendKeys "{DOWN}", True
Wend
rr = ActiveCell.Row
Range("A" & r & ":A" & rr).Copy
Sheets("Teams").Select
Select Case n
Case Is = 1
Range("A3").PasteSpecial
Case Is = 2
Range("D3").PasteSpecial
Case Is = 3
Range("G3").PasteSpecial
End Select
Next
Sheets("SecResources").Select
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Sheets("Teams").Select
Range("A13").Select

End Sub
[/vba]

[Kicker]

MD
thanks. I use your method all the time and actually have it several times in other places in the project. However, I startd this section late at night and have absolutely no idea why I used sendkeys. Normally don't for this type of movement.

[johnske]

...
Range("A1").Select
SendKeys "{DOWN}", True
r = ActiveCell.Row
While Not ActiveCell.Value = ""
SendKeys "{DOWN}", True
Wend
...
[/vba]

Aren't you just using that to replace Range("A2").End(xlDown).Select ?

[lucas]

Aren't you just using that to replace Range("A2").End(xlDown).Select ?

Glad I didn't have 2007 when I ran it....