@Willie- 1) I've suggested Zakrock tries your solution (I had something else in mind but no time). 2) Could you do me a favour please? I gave a solution to loop through subfolders to move pdf files but the user Maklal said it didn't do what I said. Could you please review by answer, try the file and report in that thread what you found? Thanks in advance. 3) I reported your recent rude user to Don and he confirmed that user has been banned from the Forum.
John_Ru (rep: 6142)
Jun 13, '23 at 2:32 pm
@John - 1) Thanks for the props. As always with Excel and VBA there is more than one way to achieve the same result. Hopefully zacrock will respond. 2) There is a lot to digest there. I will review and test on my laptop when I have more time and report my findings. 3) I too had advised Don.
Cheers :-)
@Willie 1) Agreed 2) Many thanks in advance 3) Good. You did everything right and didn't derserve that response. Shocked me.
@Zakrock - please respond to Willie once you've digested it. Sorry to use your question for some side issues (2 and 3 above)
John_Ru (rep: 6142)
Jun 13, '23 at 5:26 pm
WillieD24
Thank you so much for the above files etc.
I have been away so not had chance to look at your code also will be away for another 10 days. But will have a look as soon as I get back
Kind Regards
zacrock (rep: 6)
Jun 18, '23 at 7:24 pm
Hi
SORRY to be so long in getting back to you
I have now got the files BUT got a problem
When I run the excell files that you so kind gave me to download I cannot see any code that I can alter and copy to put in my dates etc etc
so I can then copy the code and put it in a vba file in my work sheet
I did a ALT F11 but still could not see any code
what have I got wrong please
zacrock (rep: 6)
Jul 4, '23 at 9:03 am
Zacrock
Open the first file Master File - Time Limited Ver1.xlsm, use Alt+F11 to open VB Editor then look in the Modules folder (in the Project Explorer pane) - you'll find the code in that.
Willie's Answer seems a very useful answer to me!
John_Ru (rep: 6142)
Jul 4, '23 at 10:19 am
Thank you I ave now found the code and all works great
BUT BUT if they put there clock and date back they can get into the code and alter the dates to say 10 tears time.
Is there any way I can stop them from putting thr clock back
Also i do not know if this can be done
they have put loads of information into the work sheet and have come to the end of the trial. they then say they would like to keep the program.
1......if the code still can be got at with putting the clock back no problem
they can just send us a copy of the work sheet and I can alter it
save it and give them a new copy.
2.....BUT if there is a way to stop them putting the clock back then of cause if
they cannot see the code so nor can I so what would happin to all the
info they have put in, unless there is another way for me to get at the code.
I thougt they was a way that you could be sent a email when some one replys to a post
Thank you all
zacrock (rep: 6)
Jul 7, '23 at 3:38 pm
Anyone got a answer to this please?
zacrock (rep: 6)
Jul 11, '23 at 6:35 pm
@zacrock
I understand your concern but you may be worrying needlessly.
I doubt that someone would turn their date/time back as doing so would affect ALL programs on their machine. Changing their date/time would probaly have undesireable results in other programs. I suppose it is possible, but highly unlikely. I don't know of any way that you can make changes to their machine.
If you look at the code, when the trial ends code runs to lock ALL cells and protect the sheets so the user is unable to do anything except look at what has already been entered - no data is removed. If they want to buy your program and not lose the data already entered they can email it back to you so you can remove all protection methods and then email it back to them.
The only thing you need to do is change the passwords I used to something of your chosing (and not mentioned in a posting)
Cheers :-)
Hi Willied24
Many thanks for getting back to me
Okay I will try that out and see ow it works
Just thought that they could turn the clock back to by pass the date then keep using it
Like you say "you may be worrying needlessly."
Thank you for your time with me
zacrock (rep: 6)
Jul 12, '23 at 4:54 am
@Zacrock- I agree with Willie. Unless you tell the user how the demo will time out, I can't see them ruining their system timing just to avoid it expiring (/getting locked)
John_Ru (rep: 6142)
Jul 12, '23 at 6:31 am
Hi
This what I have done.
1....Opend up excell 2007
2....Made a workbook
3....saved as a test.exls
4....open up test.exls
5....pressed alt+f11
6....pasted in workbook your code also select workbook and insert model and pasted your code
7....Saved as a test.xlsm
8....Opend the main excell.exe
9....Gone to options and made certain that the macro is set to
disable macros
10...pressed alt+f11 then set time back one hr from existing time
11...Ran the test.xlsm the message box comes up "with your time is up etc"
12...I click ok a box comes saying "run time error 9 subscript out of range run time error 9
13....I press debug and get "Worksheets("Sheet" & i).Select" I have altered this to how many
sheets I have like " lock all cells and hide formulas Do Until i > 14"
14....I have 14 sheets in my workbook
I have been on the net to see if I could see what is causing that errow but did not understand it
Sorry to say I'm stuck now.
I do want to know 3 more things about your script but do not want to complicate things
I thank you VERY MUCH for your time and trouble with me
To save you looking here is your vba code
Hope i post this correct
zacrock (rep: 6)
Jul 15, '23 at 4:13 pm
Zacrock. I leave Willie to reply on this but you say:
12...I click ok a box comes saying "run time error 9 subscript out of range run time error 9
13....I press debug and get "Worksheets("Sheet" & i).Select" I have altered this to how many sheets...
You will get that error if any of you sheets is not named like Sheet1 (when you look at the tabs). If that's the case, you could use Sheets(i) instead.
Also you say ".To save you looking here is your vba code" but you didn't include that (or your variant) for Willie.
Finally, did you try my Answer to your later question?
John_Ru (rep: 6142)
Jul 15, '23 at 5:21 pm
Hi John
sorry I'm new to this forum and it is very differnet to others I have used.
On the others I could select the person then select reply with quote
But on this it is differnet any way I hope I have got this correct.
you say
Finally, did you try my Answer to your later question?
Did you mean this one?
Open the first file Master File - Time Limited Ver1.xlsm, use Alt+F11 to open VB Editor then look in the Modules folder (in the Project Explorer pane) - you'll find the code in that.
If so yes thank you and now can see the code
Also you say ".To save you looking here is your vba code" but you didn't include that (or your variant) for Willie.
Yes I did load it in that post by clicking the code icon then pasting the code in
BUT it said that there is to much text in this messsage.
Do I have to post the code on its own?
many thanks
zacrock (rep: 6)
Jul 16, '23 at 6:27 am
Zacrock. I don't know what the message limits are but if it tells you the text is too long, remove the code, post that message then add the code to a separate follow-on post.
By your later question I meant save data after time out has gone
John_Ru (rep: 6142)
Jul 16, '23 at 9:23 am
Hi John
Hope i have done this correct.
Could not find a way to upload the file.
so i will upload the two codes if you look in the vba code you willl see wher the code is from
will try to load code now
zacrock (rep: 6)
Jul 16, '23 at 3:33 pm
Too long. Comment can be only 1500 characters in length.
zacrock (rep: 6)
Jul 16, '23 at 3:35 pm
so unless I can find a way to get you the file or code I'm stuck
zacrock (rep: 6)
Jul 16, '23 at 3:36 pm
This is the file that I'm useing
https://www.teachexcel.com/talk/file.php?f=2905
zacrock (rep: 6)
Jul 16, '23 at 3:38 pm
You can add a second file to your question but also advise Willie of that your issue
John_Ru (rep: 6142)
Jul 16, '23 at 3:40 pm
Looks like our posts were close and crossed paths but the file you refer to (https://www.teachexcel.com/talk/file.php?f=2905) is from Willie's reply I think. I'm not sure how that helps Willie or you since that doesn't have the 14 sheets you referred to earlier so we still can't see how your sheets are named.
John_Ru (rep: 6142)
Jul 16, '23 at 4:19 pm
Hi John
This getting a bit confuseing ha ha ha
Sorry about the link BUT as I said untill I can find out how to upload a workbook or a zip file to get over the "Too long. Comment can be only 1500 characters in length."
I cannot send you the wookbook.
the link is to show you what code I used from that file only NOT the workbook
zacrock (rep: 6)
Jul 16, '23 at 7:24 pm
Zacrock. Thing is that the VBA code works with your workbook so errors can occur because of the structure of your workbook or contents of your cells.
The code seems okay (Willie's demo works) so suspicion falls on your workbook.
Earlier you said you used a Do Until loop with:
Worksheets("Sheet" & i).Select
so you need an index incrementing statement within that loop:
i = i + 1
to progress through your 14 worksheets AND those worksheets to be named Sheet1, Sheet2 all the way up to Sheet14. If one is missing or has any other name, it will cause the runtime error 9 at the point VBA fails to find a sheet named like that. My guess is you'll have sheets with meaningful names in you file e.g. "Summary Report".
I did tell you how to correct that, if that is the problem. Also please note that you generally don't need to select a worksheet in VBA, you can loop through them and act on them without selecting them (although you might want to change the sheet selection to show the outcome of your code).
It would be a pity to waste Willie's eork here but I'm struggling to see how we can help you further.
John_Ru (rep: 6142)
Jul 17, '23 at 2:48 am
John
How do I upload a file on this forum?
As on other forums all I do is select a upload or a paper click. but cannot see them any where on here.
SHEET NAMES
Yes you are correct names are like
sheet1 (jan)
sheet2 (feb)
etc etc
"It would be a pity to waste Willie's eork here but I'm struggling to see how we can help you further."
Sorry do not follow you.
Do you mean to get in touch with Willie?
zacrock (rep: 6)
Jul 17, '23 at 4:17 am
Zacrock. To upload a file, go to your original question, choose Edit (below the text) then use the Add Files... button to attach a representative Excel file. Edit the question wordong to say how its revised (e.g. what's attached) then press Update to complete.
Try to do this with any future questiobs - it nearly always saves us time and leads to a quicker solution.
I won't be contacting Willie directly- we know each other only through the Forum but we've never spoken and don't know the other otherwise (e.g. there's no mechanism firt PMs and we keep our email addresses private on the Forum).
John_Ru (rep: 6142)
Jul 17, '23 at 7:03 am
Also remember that Willie provided a solution and is better placed to help you but anything he does is in his own spare tine (likewise for me) so we might give up at any time for any reason (e.g. some users are too demanding and too many fail to respond, annoyingly fir us!)
John_Ru (rep: 6142)
Jul 17, '23 at 7:07 am
Hi John,
As I said very new to this Forum and still getting my head round it.
You said
"Also remember that Willie provided a solution and is better placed to help you"
In fact I thought I was talking to him BUT you got the post so I was total confused and stilll am how to post to one person. on this forum.
On other forums I just goto where they replyed and then select quote that then put the text in wrapped in quote . i then put in my text.
zacrock (rep: 6)
Jul 17, '23 at 9:04 am
So I goto willie post and just put Discuss and that will goto him and no one else
About ulpload if i goto my first poast on here that is where i upload my files then?
will they then goto willie OR any where.
JOHN
I do understand about your time and see your point about
"some users are too demanding and too many fail to respond, annoyingly fir us!) "
So sorry to be one of those, it is so hard if you do not undersstand
Okay I will try to get in contact with willie from his post at the top
ONCE AGAIN MANY MANY THANKS FOR ALL YOUR TIME
zacrock (rep: 6)
Jul 17, '23 at 9:11 am
I will do a test file to upload and see where it goes
zacrock (rep: 6)
Jul 17, '23 at 9:11 am
Hi Willie
I hope you get this and I have got it to the right place
I have your workbooks that you sent to me thank you very much for them
I have a few problems I would to get correct if you do not mind
Kind Regards
zacrock (rep: 6)
Jul 17, '23 at 9:22 am
@Zacrock (you can tell it's me, John, from the bottom of the post).
Firstly when I asked about sheet names, you responded above that they are like:
sheet1 (jan)
sheet2 (feb)
by which I hope you mean the tab covering January appears as sheet1 but not as sheet1(jan) or jan - either of those would cause the problem I suggested.
Please note that this is a public Forum so any user can see and comment on any post. There's no direct one-to-one communication (though in fact very few people answer questions routinely, Wille being one).
If you look at other questions, you will see that Willie and I sometimes co-operate / comment on new questions and each other's Answers (and you'd have seen where files appear in a question).
Posting a test file proves the latter to you but doesn't help Willie to see what your problem might be, sadly.
You added "it is so hard if you do not understand " but it's similar for us when you don't post a representative file. Trust me (or look at the Top Users by Points table), I've understood and solved problems from hundreds of users of varying abilitlities. I have more spare time than Willie so try to help everyone by responding to gaps in progress (if I feel I have a valid, helpful suggestion.
@Willie - kindly look over the recent posts (and apologies if my intervention has made things trickier). I'm happy to step away from this question and leave it to you but interested to see if you resolve it, not least since I posted an Answer to Zacrock's later question but he feels he can't respond to that one until he's resolved this one).
John_Ru (rep: 6142)
Jul 17, '23 at 10:45 am
John
Im total confused now , mind you that does not take much at 82 years the old grey matter does not keep a lot ha ha ha
I presume that when you put this
@Willie you are talking to willie
Tell you what if it is okay with you when I get back I will post you the workbook then you can see for your self what it is
Thank you
zacrock (rep: 6)
Jul 17, '23 at 2:03 pm
I'm impressed you're still working Zacrock- makes me feel like a slacker! A helpful one though hopefully.
Yes, @name is fairly conmon as a way to differentiate between addresses. Your file would almost certainly help but remember to remove personal data (or commercially-sensitive stuff), remembering now this is a public forum.
John_Ru (rep: 6142)
Jul 17, '23 at 3:27 pm
Hello john
well here it is hope it will help to see my problem.
I'm not to certain who can see the workbook so put a password on it
Thank you
zacrock (rep: 6)
Jul 17, '23 at 5:05 pm
Thanks for uploading that new file. It's late here so I'll look tomorrow. So long as you didn't password-protect your VBA project, Willie or I should be able to follow what's going on. You might want to delete it after you get a response that sorts it
John_Ru (rep: 6142)
Jul 17, '23 at 5:47 pm
John
Okay it is 11.40 pm here in uk
I did not know who could look at the file so i DID put a password on it to be safe from all copying it BUT if it has to be open then so be it#
unless I can get the password to you some how
My eyes are closing so will say good night to you and see you to
Kind Regards#
morrow
zacrock (rep: 6)
Jul 17, '23 at 6:39 pm
Tell you tomorrow. I'm in the UK too
John_Ru (rep: 6142)
Jul 17, '23 at 6:50 pm
Morning. Thanks for attaching a file but I can't open it without the password.
Unless someone other than Willie is following this discussion closely, it's probably safe to give me the password in a post here (not in the question) and I'll tell you I've got it. You can then remove that post if you want do (which may delete the subsequents posts, hopefully just my "I've got it" one).
I'll look for your post when I'm back, late morning UK time unless plans change.
John_Ru (rep: 6142)
Jul 18, '23 at 2:43 am
Thanks, got that but my return home is a bit delayed. Should have an hour free time so will check and let you know then.
Willie is in North America so wouldn't see for some hours (though haven't seen posts from him for the past few days)
John_Ru (rep: 6142)
Jul 18, '23 at 6:10 am
okay did you get the workbook if so can I delete it or leave it on
Thank you
zacrock (rep: 6)
Jul 18, '23 at 7:08 am
Zacrock (also John?!). You posted a file with no macros but that's enough to see the problem (and you can probably delete it now)...
I just looked at your file and seen what I suspected- your sheet names are NOT like Sheet1, even though you might think so!
If you look in VBA, you'll see sheets labelled like:
Sheet1(JAN)
The second bit (in bold, JAN) is the sheet's Name - that's what you see as the "tab name" when you view the workbook normally in Excel and can be changed by the user (unless you prevent that). That's also the bit used in VBA lines like:
Worksheets("Sheet" & i).Select
so, with that, VBA skip the sheet named JAN and fail to find one with a Name of Sheet1 (say, when i=1) and produce that runtime error 9.
The first bit above (Sheet1) is the sheet's CodeName - that's set by VBA and doesn't change (/ can't be changed even using VBA); a very different thing which has an advantage since you can address the same sheet using that code name even if the user changes the sheet name.
(Sheets can also be addressed using their Index value- where they are in the Collection - which can be different for code names and names- but let's not worry about that!)
(see follow-on post...)
John_Ru (rep: 6142)
Jul 18, '23 at 8:02 am
( ... first post to the above)
To demonstrate that, try this small macro in VBA (making sure you can see the Immediate window there, or replacing Debug.Print with MsgBox):
Sub GetSheetNames()
Dim ws As Worksheet
' list all sheet names in the Immediate window
For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
Next ws
End Sub
That window (or message boxes) should show:
DEC 21
JAN
FEB
MARCH
APRIL
MAY
JUNE
JULY
AUG
SEPT
OCT
NOV
DEC
Sheet13 (4)
where the order mirrors the tab names (and depends on the Index I mentioned). Swap the tab order and the results will echo that change.
(see follow-on post...)
John_Ru (rep: 6142)
Jul 18, '23 at 8:05 am
(... third and final post to the above
Given you want to lock all sheets (I think) you can use that same approach in your code (replacing the Debug.Print... line with code to lock the sheet), since it captures all sheets, no matter what they are called.
Hope this makes sense and enables you to fix your implementation of Willie's code. Two minor points though, both in sheets from JAN to DEC (bit not DEC 21) -
1) cells S3 and S5 include "OVERHEADS" but "overheads" is just one word
2) cell A10 says "DO NOT DELETE THIS LINE"; fair enough but that's unnecessary if the worksheet is protected to prevent that (like in the file you posted).
To be honest (though without seeing any code you provide), this workbook looks fairly simple so I'm suprised you seem to have a potential buyer for it. Good luck nevertheless.
Finally, thanks for marking Willie's Answer as Selected (it's the only thing he gets for his efforts!), I hope you can do the same for my Answer to your follow-on question.
John_Ru (rep: 6142)
Jul 18, '23 at 8:05 am
John so sorry to say about the code , I thought I had done it ,
But befor I go any further I have posted it again BUT with code in it.
Please cacn you have a look at that first and run it
same password
zacrock (rep: 6)
Jul 18, '23 at 11:12 am
No chance today, sorry. I won't be back until late.
Did you follow my lengthy explanation of the (or at least one) issue?
Also if you change that small macro above from ws.Name to ws.CodeName, you'll see the difference.
John_Ru (rep: 6142)
Jul 18, '23 at 11:29 am
"No chance today, sorry. I won't be back until late. "
No problem John in no rush
"Did you follow my lengthy explanation of the (or at least one) issue?"
Yes (well nealy) BUT how do I get over the sheet name problem?
"Also if you change that small macro above from ws.Name to ws.CodeName, you'll see the difference. "
When I do
Debug.Print ws.Name
nothing shows. "Immediate window " I presume main workbook window
change to msgBox that shows one at a time
see follow on
zacrock (rep: 6)
Jul 18, '23 at 5:00 pm
"To be honest (though without seeing any code you provide), this workbook looks fairly simple so I'm suprised you seem to have a potential buyer for it. Good luck nevertheless."
ha ha ha yes all things are simple when you know how
"potential buyer"
Do not forget that they do not know anything about computers.
DO not how that box came in sorry
"Finally, thanks for marking Willie's Answer as Selected (it's the only thing he gets for his efforts!), I hope you can do the same for my Answer to your follow-on question."
Did not know I did that
"I hope you can do the same for my Answer to your follow-on question."
Yes with no problem when I no how
Is there a help file how to use this part
zacrock (rep: 6)
Jul 18, '23 at 5:11 pm
Zacrock. Just home so quick comments on your posts (this discussion must be getting to record length!)...
1) .The "Immediate window" in NOT the "main workbook window". It's part of VBA. From Excel as normal, press Alt+F11- this displays the VBA window and projects. If you don't see the Immediate window on the right somewhere, go menu View then click "Immediate window". Run the macro and look in it.
2) The "box came since you selected some typed text then accidentally clicked the CODE button
3) We get NOTHING for helping you (apart from kind words and gratitude sometimes)
4) You said "Yes with no problem when I no how; Is there a help file how to use this part" - I don't really know; I've ansered over 700 questions here but only ever asked two. I got an answer for only one and it was easy to mark it as Selected. Look at the Rules (look for link near top right) or asl me when you've reviewed my Answer.
5) I took a quick look at your new file. The Workbook_Open macro (which fails on file open) shows the problem I tried to explain earlier. The sheet names and method may be appropriate to Willie's trial version but not here since you don't have tab names like Sheet1 as I explained. I'll see if I can find any time tomorrow to fix it but this is consuming a lot of time, with no real gain for me!)
John_Ru (rep: 6142)
Jul 18, '23 at 6:02 pm
Okay John, I understand this a very long one. and you do not get paid for your time.
BUT if at any time you think you have done more than enough then please say and I will end it.
The other thing is I'm MORE than willing to pay for your time.
I have been done on a site that gives people to help with VBA coding etc . they took my money and did not solve my problem then went off.
"The "Immediate window"
arrrrrrrrrrrrrrrrrrrrrrrrrr got it now and YES it list them all now
see second part
zacrock (rep: 6)
Jul 18, '23 at 6:59 pm
John
I wish to thank you VERY VERY MUCH for all your time with me.
I'm very sorry to be so thick at this and take a lot of time understanding things.
What seams so easy to you and staright foward it comes a bit more to me
Anyway at any time please do say enough is enough and I will understand
Kind Rergards
zacrock (rep: 6)
Jul 18, '23 at 7:03 pm
Thanks Zacrock. VBA is quite tricky when you begin (as I know well) but it's a great asset. I'm not giving up on you just yet! Time to sleep...
John_Ru (rep: 6142)
Jul 18, '23 at 7:07 pm
Zacrock. I think it would save time to have a chat with you before I try to fix your file (e.g. what does your other code do? Does it force file opening with macros enabled?). If you have a business phone number, please post it below and I'll reply later then call you today. After that you can delete that post.
John_Ru (rep: 6142)
Jul 19, '23 at 6:13 am
John
build56@hotmail
If needed I then can give you my telephone number
BUT just recap as this has got more and more confusing to me
1....All I wanted was a vba code to say
2....this is a demo and you have one month from to days date to try it out then it will say you have xxx days left after the days have gone then it would say
sorry your demo has timed out please contact the programer then iit would close the file
That is when I found willie code
BUT it went on from that to say about I must have macros enabled and just lost me
so from a simple code to confused
zacrock (rep: 6)
Jul 19, '23 at 6:40 am
Understood. Like you I'm reluctant to reveal my email but I just sent you an email with the subject "For Zacrock (TeachExcel)" so you know it's me.
John_Ru (rep: 6142)
Jul 19, '23 at 7:34 am
@John
Regarding your post on July 18 @ 6:10 am:
" Willie is in North America so wouldn't see for some hours (though haven't seen posts from him for the past few days) "
More specifically, Ontario, Canada. And I don't always have a chance to check the forum daily; plus you seem quite involved so I decided to leave this one for you to continue with. But if you ever want my 2-cents, just let me know (keeping in mind that it may be a ay or two before I respond).
Cheers :-)
@Willie - I know your location (you told me before) but chose not to reveal it to Zacrock, to protect you!
Thanks for offering to help but I don't think I'll need it here. Furthermore I actually spoke to the (UK) user who's now on vacation for a week or so..
John_Ru (rep: 6142)
Jul 21, '23 at 1:15 pm
Hi John
I'm back in the land of living now.
so if you would you like to continue that is okay with me
zacrock (rep: 6)
Aug 1, '23 at 8:49 am
Hope you had a good break. I'm back late tomorrow so will try to pick it up on Thursday.
John_Ru (rep: 6142)
Aug 1, '23 at 11:44 am
okay thank you
zacrock (rep: 6)
Aug 1, '23 at 7:29 pm
Hope you had a good break. I'm back late tomorrow so will try to pick it up on Thursday.
John_Ru (rep: 5437) Aug 1, '23 at 4:44 pm
Hi John
Any news for me?
thank you
zacrock (rep: 6)
Aug 7, '23 at 10:05 am
Sorry to disappoint you but have had unexpected issues. Should have some free time tomorrow to do it.
John_Ru (rep: 6142)
Aug 7, '23 at 10:35 am
okay no problem John
when you are ready
zacrock (rep: 6)
Aug 7, '23 at 6:39 pm
Hi John
Any news yet?
zacrock (rep: 6)
Aug 9, '23 at 3:31 am
I emailed you last night to say I'd be delayed
John_Ru (rep: 6142)
Aug 9, '23 at 5:14 am
Hi John,
Hope all is okay
kind regards
zacrock (rep: 6)
Aug 11, '23 at 5:06 am
John, see private email send just.
John_Ru (rep: 6142)
Aug 11, '23 at 6:47 pm
