PDA

View Full Version : Solved: Problem Moving Code from Module to UserForm



Kicker
02-19-2007, 10:56 AM
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...:dunno

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
02-19-2007, 11:17 AM
Thanks for noticing....
Probably not a compatability problem......blew up????:SHOCKED: Seriously what really happened...what was the error, etc.

Kicker
02-19-2007, 03:25 PM
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
02-19-2007, 03:31 PM
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
02-19-2007, 03:36 PM
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
02-19-2007, 04:11 PM
:bug: 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
02-19-2007, 04:59 PM
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

While Not ActiveCell.Value = ""
Dim tmp
tmp = ActiveCell.Address
SendKeys "{DOWN}", True
Wend

Try While Not ActiveCell.Value = ""
ActiveCell.Offset(1).Activate
Wend

lucas
02-19-2007, 05:04 PM
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.
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

Kicker
02-19-2007, 05:14 PM
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
02-19-2007, 06:34 PM
...
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
02-19-2007, 08:27 PM
Aren't you just using that to replace Range("A2").End(xlDown).Select ?
Glad I didn't have 2007 when I ran it....