Well I've tried and tried and tried but I can't figure it out.
Does anyone knoe how to get a range to display in a userform.
Not just the contents of the range but the actual range with the values etc, so that it looks just like the worksheet range.
I've tried using a control called MSHFlexGrid but can't get the range values assigned to it. There's got to be a better way or something I've totally missed???
Thanks
AJW
Try this link for some more information on system resources and Memory.
http://support.microsoft.com/d…px?scid=KB;en-us;q183503&
Regards
AJW
It's about 30KM from me.
I'm in a place called Bedfordale which is inland from the coast a bit. That way when the polar caps melt I'm on a prime forshore property 
All part of my very very very long term investment stratergy.
Tony.
Insomniac,
Below is a hotch botch of hobbled together code from various sources plus some of my own ingenuity.
You may also find J-Walks site of interest on this topic.
So far from my searching no one has been able to figure a work around for doing what you and I want. It's especially more difficult if not (dare I say) impossible to do this with a hidden worksheet / workbook or addin.
But luckily I don't understand impossible 
Hope it helps.
1. J-Walks bits:
http://www.j-walk.com/ss/excel/tips/tip82.htm
2. My bits: (not as clean as J-Walk and others, but hey I'm fairly new to the VBA thing).
I'll break it down to steps for easy of explanation.
A. Open Two New Workbooks.
B. In the first Workbook under Module 1 place the following function. (I can't remember who's work this is but it's not mine.)
Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error will occur because there is
' some other problem accessing the file.
On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.
' Check to see which error occurred.
Select Case errnum
' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False
' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True
' Another error occurred.
Case Else
Error errnum
End Select
End Function
C. In the first Workbook under Module 2 place the following code. (This is mostly my hobbled code.)
Sub CheckOpenUser()
On Error Resume Next
Application.ScreenUpdating = False
' Specify the Path to the file of your choice
If IsFileOpen("P:\Yadda\Yadda\Allocation Numbers AXXXXX.xls") Then
Application.ScreenUpdating = False
Application.Workbooks.Add
Application.ActiveWorkbook.Names.Add Name:="UserName", RefersToR1C1:="=Sheet1!R1C1"
'Specify the Path to the file of your choice and the Range Name UserID
Application.ActiveCell.FormulaR1C1 = "='P:\Yadda\Yadda\Allocation Numbers AXXXXX.xls'!UserID"
Calculate
'Fairly straight forward I'm telling it that the UserID = the UserName
UserID = Application.Range("UserName").Text
'A message to give the info
MsgBox "The Allocation Register is currently being used by: " & UserID & vbCr & vbCr & "Please try again later.", vbInformation
Application.ActiveWorkbook.Close savechanges:=False
Else
Workbooks.Open filename:="P:\Yadda\Yadda\Allocation Numbers AXXXXX.xls"
End If
End Sub
Because I'm running my code from an addin I don't have a visible Worksheet to capture the returned Name and feed through to VBA, so I simply make one with screenupdating turned off so that no one sees it appear and then when the message has been read I dump it.
D. In the Second Workbook ( the one you want to check if it is open and who has it open) place this code under "This Workbook"
Sub Workbook_Open()
'Spec up the user
Users = ActiveWorkbook.UserStatus
'Spec in the sheet and range for the UserID, My Sheet name = Allocation No. , Range name = UserID (don't forget to name the range !)
ActiveWorkbook.Sheets("Allocation No.").Activate
'UserID = the user name
Application.Range("UserID").Value = Users
Calculate
Application.StatusBar = "Updating Records.........Please wait."
'just something that needed to be done
ActiveWorkbook.Save
Application.StatusBar = ""
End Sub
Now what should happen is that when you run CheckOpenUser.
1. It will check to see if the file is open by someone else
2. If open it will bring back the username to the temporary workbook and from this fill in the message box.
Hope it works.
Regards
Tony
"(unfortunately, my imagination often far outstrips my coding ability !!!) "
Tell me about it, My wife says I'm a dreamer but lack the talent to accomplish the task. Darn, don't you hate it when they are right . Sometime I feel like the scarecrow in the Wizard of Oz "If I only had a brain", well maybe if I only had a brain with the cognitive skills to match my imagination.
I like the sound of the ships in battle, your certainly aiming high to try to map it all out in 3D. But hey that's half the fun of it - the challenge!
Regards
AJW (Tony)
QuoteOriginally posted by Chris Davison
when I see stuff like this it makes my mind race.....
:guitar:
I'm much the same Chris, and I can't resist the temptation to fiddle.
I thought about writing a VBA loop to loop through the scroll bar values so as to animate the graph, but decided on the attached instead.
http://ozgrid.com/forum/viewth…tachment&tid=679&pid=3439
Must say that I'm immensly impressed with the work Andy has done to produce this. Only last night I was wide awake at 3AM wonder how I could do something similar and hey presto I find it already here!!
Slightly depressed now because I was looking forward to the challange 
Oh well I'll have to come up with my super 3D link mapper instead
AJW
Sorry forgot to attach my fiddle.
WAY CLEAVER 3D CHART.
From a quick look at it he's used a standard 2D scatter and superimposed an X,Y,Z plane using line graphs. He's then kept the spacial relationships between the plotted points and the planes through the SIN, COS calculations, Scroll Bar values and derived values.
That is sooooooo cool.
I'm jealous.
AJW
Sorry but I couldn't resist having a play with Andy's incredible creation.
Simply regraphed it on a seperate page did a bit of colouring in and put in some new scroll bars to allow for real time updated viewing of the graph as it rotates around.
Man alive I'm impressed with this thing and can think of a zillion uses. Might use it to plot out planetry movements around the sun.
AJW
CharlesW17,
If you want to have the activecell move down after entry for all the columns in your worksheet you could use one of the following:
1. This is a two step method where we manipulate the MoveAfterReturn.
'This turns it on when
'you activate the sheet.
Private Sub Worksheet_Activate()
With Application
.MoveAfterReturn = True
.MoveAfterReturnDirection = xlDown
End With
End Sub
'This turns it off when you
'deactivate the sheet.
Private Sub Worksheet_Deactivate()
With Application
.MoveAfterReturn = False
End With
End Sub
2. One step For / Next modification of Daves suggestion.
'there are 256 columns in a sheet
For Column = 1 To 256
Target(2, 1).Select
Next
Hope these also help.
Regards
AJW
DHO!!
Just figured it out.
I'm using a space in the file name which Wscript is interpreting as a \ .
Which means it was looking for a directory instead of a file.
My correct code should now look like this:
Shell "wscript C:\Windows\UpdatePackage.vbs"
and not this:
Shell "wscript C:\Windows\Update Package.vbs"
Regards
AJW
Thanks Fwind,
I've tried that for Windows 98 but it didn't work.
Shell "wscript C:\WINDOWS\Update Package.vbs"
Even tried moving the *.vbs file around to different locations and repathing the command but the same result.
Keep getting an error message saying:
"There is no file extension in "C:\WINDOWS\Update"
Maybe I'm pathing it incorrectly ??
Regards
AJW
Try this site by Dermot Balson. He's a great guy and some of his ideas may help you out.
http://www.webace.com.au/~balson/InsaneExcel/Encryption.htm
AJW
Hi All,
Does anyone know how to trigger off a Visual Basic Script from VBA.
I need to carry out a File System Object command outside of Excel (so the user can keep working) and have written a small VBS to do this. The problem I am having is trying to get it to run from VBA.
Thanks
AJW
"BTW, the person who removed my signature textbox from the file didn't bother to change the file attributes which still lists me as the author and has the link to my website. They also didn't notice they allowed their real name to be listed as the person who last saved the file."
"That must be embarrassing. "
Hi Aaron,
I hadn't read the rest of the posts before posting my last response.
I was not worried about my real name showing up as there was no malicious intent involved in my mistaken post. Please be confident in that fact.
If I had wanted to try to claim authorship or ownership of your work I would have been a little more careful in the disguising and posting of it.
Thanks AJW
XL-Dennis,
I accept your flaming in good humour see.
As pointed out to Aaron it was a mistake.
Regards
AJW
Dave,
I would like to know how some people have gotten hold of my email address when I specifically didn't tick the "Make e-mail address viewable by others?" option ?? :puzzled:
Thanks
AJW
QuoteDisplay MoreOriginally posted by Aaron Blood
Please don't remove the signature textbox from my work and post it as your own.
Thanks,
Aaron Blood
P.S.
If anyone would like to download the example they can get it here.
Aaron,
Please accept my apologies for posting your work. I was not attempting in any way to claim credit for it. I'm acutaly a little confussed because I don't know why it way attached to my post, it's not even in the context of the thread regarding *.exe and excel files ?????? My guess is that I hit the wrong file for the attachment ??? Or that I make the wrong file into an *.exe for an example ??? Whatever the case may be I apologise unreservedly and can only hope that the link that Dave has included to your site will help remedy the situation. (thanks Dave)
Regards
AJW
:flame: Flame those suckers who bag your name :flame::flame::flame:
Stick with it, if they have a problem let them deal with it.
AJW
And no I'm not a "JW"
Hi All,
Just as a suggestion and to assist with the answering of questions it would be worthwhile to include the following basic information when an question is posted:
1. Operating System / Version.
2. Excel Version.
This might help us all out, perhaps Dave you could include it as defaults that need to be completed with every new topic posted??
Just a suggestion.
Take Care
AJW
OK, I've been researchingthis topic further based on a hint given in Insomniacs post about Binnary File Format.
Apparently from what I've picked up using VBA you can save the file as a Binary File?? to conserve file space and them somehow open it again??
Does anyone have any insight into this ??
Thanks
AJW
Insomniac,
Don't like them for opening Workbooks because of this problem. Great for linking to documents, PDF's, images etc but not workbooks.
Are the workbooks your linking to to open in a fixed location ? If so get rid of the hyperlink and use an object like a transparent control of some sort and overlay it on the background text. You can then hard code (1.) the filepath & name to open on that control, this allows the workbookopen events to trigger (your macros). If your workbooks are randomly located and may move between sub directories within a master directory the you can still code the directory to search (2.) and the file name to open as per attached examples. You'll need to read between the lines and extract what works for you, It's a little messy as it has evolved over a period of time and I haven't caught up with the housekeeping
(1.) Sub EquipSummary_Open()
Application.ScreenUpdating = False
On Error GoTo HandleAnyErrors
Dim MY_PATH As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "This will open an Equipment Summary Sheet. Do you want to proceed?"
Style = vbYesNo + vbInformation + vbDefaultButton1
Title = "WSG-eQuote"
Help = "DEMO.HLP"
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbNo Then
ElseIf ViaModem = True Then
GoTo 20
ElseIf GetHardwareProfile = 1 Then
On Error GoTo error
ChDir NETWORK_PATH & "MARKETING\QUOTE PACKAGE\Templates"
Workbooks.Open FileName:=NETWORK_PATH & "MARKETING\QUOTE PACKAGE\Templates\Equip Summary Sheet.xlt", UpdateLinks:=0 'check your _ options for update links
Application.DisplayAlerts = False
Exit Sub
error:
ChDir LOCAL_PATH & "MARKETING\QUOTE PACKAGE\Templates"
Workbooks.Open FileName:=LOCAL_PATH & "MARKETING\QUOTE PACKAGE\Templates\Equip Summary Sheet.xlt", UpdateLinks:=0
Application.DisplayAlerts = False
Else
20 ChDir LOCAL_PATH & "MARKETING\QUOTE PACKAGE\Templates"
Workbooks.Open FileName:=LOCAL_PATH & "MARKETING\QUOTE PACKAGE\Templates\Equip Summary Sheet.xlt", UpdateLinks:=0
Application.DisplayAlerts = False
End If
Exit Sub
HandleAnyErrors:
MsgBox ("Program cannot be found."), vbOKOnly, "ERROR"
End Sub
(2.) Sub DRAWINGS_FIND()
On Error Resume Next
If ViaModem = True Then
GoTo 20
If GetHardwareProfile = 1 Then
With Application.FileSearch
.NewSearch
.LookIn = "P:\MARKETING\Drawings\"
.SearchSubFolders = True
'I'm searching for and opening a drawing based on the value of a cell
.FileName = "a" & ActiveCell.value & ".dwg"
.MatchAllWordForms = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For I = 1 To .FoundFiles.Count
'Then using a hyperlink, but you can substitute appropiate code to open once the file is found
Application.Range("A200").value = .FoundFiles(I)
ActiveWorkbook.FollowHyperlink Address:=Application.Range("A200").value, NewWindow:=True
Next I
Exit Sub
End If
End With
Else
20 With Application.FileSearch
.NewSearch
.LookIn = "C:\MARKETING\Drawings\"
.SearchSubFolders = True
.FileName = "a" & ActiveCell.value & ".dwg"
.MatchAllWordForms = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For I = 1 To .FoundFiles.Count
Application.Range("A200").value = .FoundFiles(I)
ActiveWorkbook.FollowHyperlink Address:=Application.Range("A200").value, NewWindow:=True
Next I
Exit Sub
End If
End With
End If
End If
MsgBox ("Program not found."), vbInformation, "WSG-eQuote"
End Sub
Regards
Tony Watermann
Chris,
Just a quick though, have you tried stepping through your code (using F8 key) one line at a time to see where it bugs out?
When I've had similar problems and stepped through the code the problem jumped up and smacked me right between the eyes.
I know its simple but sometimes we over look the simple in the crisis.
AJW
