Hello again,
I have a textbox in my workbook that looks something like this:
MK
19-JUN-12
I'm trying to find a way to automatically keep the date current. The initials 'MK' still need to be editable as well. Any ideas?
Hello again,
I have a textbox in my workbook that looks something like this:
MK
19-JUN-12
I'm trying to find a way to automatically keep the date current. The initials 'MK' still need to be editable as well. Any ideas?
There is so much good in the worst of us,
And so much bad in the best of us,
That it hardly behooves any of us
To talk about the rest of us.
If you put this code in the sheet object it will run every time the sheet is activated:
Remember to change the value in red to the correct cell. If you want it to run via a different method let me know.Please Login or Register to view this content.
EDIT: Didn't notice that you wanted MK to be editable let me edit my code.
If you merge the cell you want updated with the cell above it you can edit the "MK" in the cell above while the cell you want is auto updated on Worksheet_Activate. I will try to think of a better way
Please Login or Register to view this content.
Last edited by carrob; 08-03-2012 at 09:29 AM.
Hi
You can create a macro and assign a macro to the TextBox.
For example you could put this code in the macro:
Textbox1.Text = Format(Date,"dd mmm yyyy")
Hope this helps.
Good luck.
Tony
see if it helps
from textbox properties set multiline to "true";
if your textbox is in a userform you can use
if notPlease Login or Register to view this content.
Please Login or Register to view this content.
Regards, John55
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.
...enjoy -funny parrots-
Thank you carrob - I don't think that will work for me as I'm not using a cell but a textbox which can be moved around on the sheet.
ARGK - that looks close to what I'm after, thank you - but I'm having trouble assigning the code. What's the right way of doing that?
john55! How are you, buddy? Thank you for your reply - how in the world do I set 'multiline' to True?
Hi!
right click on yr textbox and see properties, then in the left side see Multiline, set it to true.
sword.jpg
Last edited by john55; 08-03-2012 at 10:21 AM.
When I right-click the textbox, the closest thing I see is 'Size and Properties...', when I click that, I don't see anything called 'Multiline' on the left.
Make sure you created an ActiveX Text Box. When you go to "Developer" and click "Insert" ActiveX controls are the bottom portion of the box that opens.
When you click "Properties" a box should open up with a list of properties and "MultiLine" will be on that list
see the test file attached
Thank you carrob, that works better.
Hmm, I think I'll have to put another textbox specifically for editable initials right above the date textbox, as I'm having trouble with the 'MK' being easily editable.
I'm still not able to get a current date to appear though. How do I apply a specific macro to the textbox? I'm not able to select the textbox in your sample at all, john55.
UPDATE: Yikes, I think I did something - I hit 'Run' in the VBA mode, and now I have the current date - but how do I select the box? I need to be able to move it around.
Last edited by swordswinger710; 08-03-2012 at 11:37 AM.
Go to the "Developer" tab and make sure "Design Mode" is activated.
Ooh, okay - that works, but will I always have to make sure that's turned on in order to move it? Is there no way a regular moveable textbox can contain a code or formula or something?
UPDATE: Also, the date isn't updating when I change my computer's date to tomorrow and open the workbook.
Yes unfortunately you will have to enter "Design mode" every time you want to move it. As for the date not updating I am not sure, it works fine for me, could you attach your workbook?
Well, I don't think that will work for me, as this textbox needs to be moved a LOT. Is there no way to have a normal textbox automatically update?
If you want you could use a Label instead of a Text Box, just change the code to this:
Also, make sure the Label does not have any code assigned to it, otherwise you may have trouble moving it around. Let me know if this worksPlease Login or Register to view this content.
Call me dumb, but I can't figure out how to add a label.
Go to the "Developer" tab, "Insert", under "Form Controls" click the Aa button called "Label"
Ah, thank you! Okay - I've attached a sample - is the date updating for you? It isn't for me - and I'm not sure why..
Last edited by swordswinger710; 08-03-2012 at 03:31 PM.
Yes it is updating for me. Just in case you weren't aware, the Worksheet_Activate is built in and runs every time the sheet is activated, so in order for the date to update it has to lost focus then gain focus, so try clicking onto "Sheet 2" then clicking back to "Sheet 1", that is how it will update. It should also update when you open the excel workbook.
as an option
Thank you carrob! I think this will work - but how do I format the text, such as font, size, positioning and the like? I cannot seem to find the Properties for a label anywhere.
Thank you as well, john55! I don't think that last one will work in my case though.
Unfortunately you cannot edit the font in a form label. However try this:
Go to the "Insert" tab and click "Text Box" you can move this around the page and change the font. You will also need to change your code to this:
You can also have the text box stay in the top left when you click with this code, just paste it under the code above:Please Login or Register to view this content.
Or if you want the top right replace the red text above with this:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by carrob; 08-08-2012 at 10:59 AM.
hi, I thought that a userform can be moved easily...
Thanks again john55, I'm sure you're right, but carrob's solution was exactly what I was looking for. Thank you both.
One more thought/question - is there a way to prevent a user from changing the date in the textbox manually? So for example, if he clicked it, a message would appear telling him/her not to? It's no big deal if that won't work, I'm just wondering.
Using the following code will make it unable to select the textbox at all, however if you are using the code I provided earlier which automatically moves the textbox you should not have a problem:
place in a module.Please Login or Register to view this content.
Thank you once again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks