User Input text Colour?

[ets2k9]

Hi,

I have a simple macro that allows the user to submit text into a cell with out modifying the existing text:

Please Login or Register  to view this content.

How can I get the 'userinput' text to appear in a different colour to the existng text? I'm trying to track the changes to the cell.

Many thanks

Steve

[Kafrin]

This code adds the inputted text to cell A1 and turns it to the colour in the palette that has index 3 (this is red in the default colours).

Please Login or Register  to view this content.

[ets2k9]

Runtime error '6'
Overflow

??

[MickG]

Hi, Try this mod to your code:-

Please Login or Register  to view this content.

Regards Mick

[ets2k9]

Hi, Try this mod to your code:-

Please Login or Register  to view this content.

Regards Mick

That almost works but it doesnt leave a space between the last word (I added the space to the code) and also highlights the last letter of the the last word

EG:

EXISTING TEXT comes out like this

[Kafrin]

I think the problem with my code was just that I used a Byte instead of an Integer for L, so try this instead:

Please Login or Register  to view this content.

The problems you're getting using Mick's modified version are:

1) L needs to be one greater than the length of the original string, so that when you colour it starts at the next character after the end of your original string.

2) Because L is greater than the length of your original string, you need to have the If statement in place to stop errors coming up - for example if the use cancels the input box or doesn't enter any text. (Although adding the extra space will help in this case, it's good practice to catch these sorts of things.)

3) If you want to colour everything from L to the end of your string, you don't need to specify the Length for Character. Specifying Length is the reason your last letter is not getting coloured; without it the colour should go all the way to the end, however many extra spaces you add in..

4) It's better to use ampersands (&) when concatenating text, rather than plus signs.

[ets2k9]

Please Login or Register  to view this content.

Thanks for the reply I opted for this method.

My next mission is transfer this macro to all the other cells I have to update. The format is the same a button to update 'K2' but I need to this across multiple tabs and rows.

With out creating 100's of macros all doing the same thing with the only difference being the tab.

Any ideas or is that clear as mud?

updated the + with &'s thanks

[Kafrin]

Well, you need to identify the cell you're updating each time, so you can't just point every button directly at the macro.

Waht you need is to modify the code to this:

Please Login or Register  to view this content.

Then create a macro for each button which contains this code, changing the cell each time:

Please Login or Register  to view this content.

[ets2k9]

I'll give it a go thank you

[ets2k9]

Thats works well - thank you once again.

Could someone tell me how I would factor an IF statement into the code to account for teh user pressing 'cancel' as a date stamp is still registered?

[Kafrin]

Cancelling returns an emtpy string, just like pressing OK without actually entering anything, so you need this:

Please Login or Register  to view this content.