I never thought it was possible. Is it?
Specifically, we want the code from this thread (the code posted by KPuls is the "final" we used.
http://www.vbaexpress.com/forum/showthread.php?t=3819
I never thought it was possible. Is it?
Specifically, we want the code from this thread (the code posted by KPuls is the "final" we used.
http://www.vbaexpress.com/forum/showthread.php?t=3819
~Anne Troy
By the way, these are worksheet events. When we click on a cell, the macro's supposed to run. I can only get a couple of them to run, and only by double-clicking the cell, another of the same type just won't run at all.
~Anne Troy
Anne,
PM Johnske. I think he's played with Excel on an FTP server. Could be wrong, but I thought he said it worked.
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
He dun bin PM'd.
~Anne Troy
Hi Anne,Originally Posted by kpuls
No, not specifically an STP server. Wot exactly's the problem? You want to download and auto-install the code module in a workbook maybe?
Regards,
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Nope. We wanna open the workbook right from the FTP server and run macros from it. Let me know if you wanna see the behavior. I'll upload to my FTP and give you the logins.
~Anne Troy
Hi Anne,Originally Posted by Dreamboat
Shouldn't be hard, but I'm not too sure about runnung a worksheet event from it.
Yeh, I'll need logins etc to test.
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Check your PMs.
~Anne Troy
Hi Anne,
Very strange, it runs perfectly when downloaded to my desktop, but sometime one or none will work when run from the link. However I got it to work every time by turning ScreenUpdating = False off.
I can't save the changes to your book on the link cos it's saying read-only, but if you put this mod into it, it should work every time (as I said, it only turns off ScreenUpdating)
Let me know,Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim i As Long Dim LastRow As Long If Intersect(Target, Range("B8,E8:F8")) Is Nothing Then Exit Sub End If 'Application.ScreenUpdating = False Application.EnableEvents = False Range("E:F").NumberFormat = "@" LastRow = Sheet7.Range("A65536").End(xlUp).Row For i = 2 To LastRow Range("E:E").Replace What:=Sheet7.Range("A" & i).Text, _ Replacement:=Application.WorksheetFunction.Rept("a", i), LookAt:=xlWhole Next i LastRow = Sheet7.Range("B65536").End(xlUp).Row For i = 2 To LastRow Range("F:F").Replace What:=Sheet7.Range("B" & i).Text, _ Replacement:=Application.WorksheetFunction.Rept("a", i), LookAt:=xlWhole Next i Select Case Target.Column Case Is = 2 Range("sfw").Sort Key1:=Range("B9"), Order1:=xlAscending, _ Key2:=Range("E9"), Order2:=xlAscending, _ Key3:=Range("F9"), Order3:=xlAscending, _ Header:=xlNo, MatchCase:=False, _ Orientation:=xlTopToBottom Case Is = 5 Range("sfw").Sort Key1:=Range("E9"), Order1:=xlAscending, _ Key2:=Range("B9"), Order2:=xlAscending, _ Key3:=Range("F9"), Order3:=xlAscending, _ Header:=xlNo, MatchCase:=False, _ Orientation:=xlTopToBottom Case Is = 6 Range("sfw").Sort Key1:=Range("F9"), Order1:=xlAscending, _ Key2:=Range("B9"), Order2:=xlAscending, _ Key3:=Range("E9"), Order3:=xlAscending, _ Header:=xlNo, MatchCase:=False, _ Orientation:=xlTopToBottom End Select LastRow = Sheet7.Range("A65536").End(xlUp).Row For i = 2 To LastRow Range("E:E").Replace What:=Application.WorksheetFunction.Rept("a", i), _ Replacement:=Sheet7.Range("A" & i).Text, LookAt:=xlWhole Next i LastRow = Sheet7.Range("B65536").End(xlUp).Row For i = 2 To LastRow Range("F:F").Replace What:=Application.WorksheetFunction.Rept("a", i), _ Replacement:=Sheet7.Range("B" & i).Text, LookAt:=xlWhole Next i 'Application.ScreenUpdating = True Application.EnableEvents = True End Sub
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Well, let's try it. I'm uploading right now.
~Anne Troy
Hi Anne,
The only thing I can think of is that the command Application.ScreenUpdating = True is getting lost somewhere in the aether... Maybe due to the difference in the speed at which the app (XL) is working and the signal download speed.
I played with this for quite a while and found that sometimes 1, 2, or even all 3 cells weren't triggering the sort, and other times they all were...
Anyhoo, can we mark this solved for now?
Regards,
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.