Hi all,
Im having a problem.. Ive never used Visual Basic and I am trying to copy and paste some code in that formats cells so I dont have to type "/" or ":" while entering dates and times. I know the code isnt working because I have "Private Sub Worksheet_Change(ByVal Target As Excel.Range)" typed in twice, but I just dont know what to type to make this work. I put in arrows to show where the error is. Any help would be greatly appreciated!
Please Login or Register to view this content.
Last edited by ericwilder; 01-21-2013 at 03:28 PM.
First off , your code should be placed in coe tags
Second for your problem as Excel.Range , remove Excel. , as Range willwork fine
Eric,
Please use code tags in your code. You can edit the above code by the following:
Choose-Edit-highlight the whole code and then click# (from the menu)
Or
Code[ ]
your code
Code[ ]
Thank you for your help hulpeloos!! Im sorry I didnt understand what you meant by "your code should be placed in coe tags"
I entered this "Private Sub Worksheet_Change(ByVal Target As Range)" as my second macro name... but it still returns the same error message.. What do I need to re name this thing to get it to work?
[CODE]
''''' your code goes in here
[CODE]
wait ... you have attempted to have both subs right under eachother in the same VBA-code sheet ?Originally Posted by ericwilder
You can normally put all your codin into 1 "Private Sub Worksheet_Change(ByVal Target As Range)"
Yea i just tried to combine two different codes I found online that I wanted to work for the same spreadsheet. I dont know how to put them together...
Last edited by ericwilder; 01-19-2013 at 04:01 PM.
in theory you can join them by remove the 2 bold lines below as indicated
HOWEVER you will havea lot off duplicates that will break the code , so you will have to rename everything in the second part , for examplePlease Login or Register to view this content.
in first part you have
So in second part you have to rename the same instacnes toPlease Login or Register to view this content.
Please Login or Register to view this content.
Thanks so much for your reply! however I am not seeing any bold text, to clarify, do you want me to delete
"End Sub"
and
"Private Sub Worksheet_Change(ByVal Target As Excel.Range) <---------------------------"
yes thats correct
Maybe change the names of the two subs:
WS_ChangeONE
WS_ChangeTWO
Then use this :
That would save a lot of renaming of variables etc .Please Login or Register to view this content.
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Hi ericwilde
Show us what you have and what you'd like it to be...perhaps we can help.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Okay, in order to avoid having to change duplicate names I took a stab at what dredwolf suggested. Heres what it looks like now.. It still isn't working though. Please look over it and let me know where I messed this up.
Please Login or Register to view this content.
you still need the (ByVal Target as Range ) for the other 2 subs is what I notice right away.. since I'm not sure what the 2 Subs are suppoesed to do, I'm not sure if there is any problems within them
But if you change the sub definitions as above, They should at least show a different error..
Hope this helps
Hi ericwilde
Post a sample workbook Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.
Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.
To Attach a File:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.
Okay I will post the sample WB
Last edited by ericwilder; 01-21-2013 at 11:03 AM.
One Sheet to Rule Them All.xls
The purpose of the sheet is to allow me to enter dates and times without entering "/"'s or ":"'s
Hi ericwilder
This code will ONLY work if you condition yourself to ALWAYS enter the DATE portion of the string with 6 (SIX) digits (eg: 011298 for January 12, 1998 or 121298 for December 12, 1998).Please Login or Register to view this content.
Thank you! However there seems to be a small issue with the code. The date appears just fine when the date and time is entered- but in the cell the time does not show up (it does in the formula bar). Expanding the cell did nothing to fix the problem. Please advise.
Also, four digit time values result in an error.
Last edited by ericwilder; 01-21-2013 at 03:14 PM.
Hi ericwilder
See attached...let me know of issues.
Ive re-uploaded the WB. I hope this helps.
You truly are a guru. I hope one day to possess a fraction of your skills. Thank you for your help.
Hi ericwilder
For the moment let's deal with the Workbook I uploaded...what's the Code doing or not doing that you wish it to?
Thanks for the compliment.
It seems to be working. I was entering spaces between the date and time which I believe is what was messing it up. The last question I have is this: if I wanted to make it so that this macro worked for both the A and B colums, what would I enter?
Would this work?
Please Login or Register to view this content.
Hi ericwilder
You can do this one of two ways (probably more).
If your dealing with the entire Columns then perhaps thisPlease Login or Register to view this content.Please Login or Register to view this content.
Okay, so I've been trouble shooting the code and there are still some glitches. It will not allow me to enter times later than 12:59. I tried entering 13:00 and it gave me the error msg. Im going to attach the sheet that I was planning to transplant the code into and let you take a better look. I wanted this code to work for columns A and B. I originally thought "this shouldn't be too hard" (how wrong I was).
Hi eric
In your Logging File do you Filter on Columns A & B? If so...how? Details please...
I have filters on both colums but I just checked with some co workers, and they are not vital and so I have removed them. I entered both versions of the code you provided that expand the macro to the A and B column, and it does not seem to be working. The A colum still operates but the B column does not work.
Last edited by ericwilder; 01-22-2013 at 12:18 PM.
on an unrelated note.. how the heck do I delete a post?! Excel Forum is only letting me edit posts (this was originally an extraneous post I wanted to delete but could not).
Last edited by ericwilder; 01-22-2013 at 12:25 PM.
Hi eric
One of the issues is the Formatting of Columns A & B in your file Logging File...that's why I asked about the Filtering of these columns. I'll look at it later this morning...have it working on my platform (if Filtering on Columns A & B is not an issue)...want do do more testing.
Hi eric
Regarding thisYou can't...only Edit. Moderators can delete posts...you'll need to ask them to do so.how the heck do I delete a post
Regarding my additional testing...what the hey...you can do that. The attached file has Columns A & B formatted as text...it seems to work on both Columns for me. Let me know of issues.
Hey, is there a way to modify the macro so it stays in military time after it is entered? Currently it reverts to standard time.
Thanks again,
Eric
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks