macro coding to show item from specified cell.

**Saky** · 01-26-2011, 06:02 AM

Hi Friends,

In col Y i have some data in my excel. I want that data's to be reflected in col F. I know that this can be simply done by giving

Please Login or Register  to view this content.

in col F1.

But i just want to know this simple thing can be converted to a VBA code.

If possible can anyone help me to do so..

Thanks a lot in advance.

Saky

**teylyn** · 01-26-2011, 06:23 AM

If you can achieve something with formulas, there is really no reason to use VBA instead.

Why would you want to use VBA over a formula?

Can you explain what you are trying to achieve? Post a workbook and explain in context.

cheers,

**Saky** · 01-26-2011, 07:06 AM

Hi Teylyn,

Thanks for your reply. Actually the reason why i am requesting for VBA code is because if i input formulas in cell it will work good but if one of my user by mistake presses Delete button on that cell then the formulas will also get deleted. TheN i have to repaste the formula in that cell. So to avoid this i am trying to put everything in VBA window.

So if you could kindly help me to convert this inti VBA code it will be very helpful for me...

Thanks for your understanding.

Saky..

**teylyn** · 01-26-2011, 07:17 AM

To prevent a user from deleting a formula in a cell, format the cell as Locked (in the format dialog, Protection tab), then protect the sheet. The user can then only change unlocked cells in the sheet.

By default, all cells are set to "Locked", so you only need to untick that option for cells the user should be able to change.

This is how Excel is intended to be used. Lock cells that should not be changed, unlock cells that are meant to be edited and apply worksheet protection (with or without a password).

There is no need to re-invent the wheel and re-create the functionality with VBA.

cheers,

**Saky** · 01-26-2011, 07:38 AM

Friend,

I am aware of this password protection. I have written other macros by setting the security levels to low. hence if i protect the cells than the macro codings are not working. Hence i am requesting for this option.

**teylyn** · 01-26-2011, 07:51 AM

This has nothing to do with password protection.

Worksheet protection prevents unintentional changes of cells, which seems to be what you want to achieve.

Post a workbook and explain in context what you want to do.

If you use macros to manipulate the sheet, you can use code to unprotect the sheet in the macro as required.

**Saky** · 01-28-2011, 10:39 AM

Originally Posted by teylyn

This has nothing to do with password protection.

Worksheet protection prevents unintentional changes of cells, which seems to be what you want to achieve.

Post a workbook and explain in context what you want to do.

If you use macros to manipulate the sheet, you can use code to unprotect the sheet in the macro as required.

Friend,

Here by i have attached my excel. please have a look.

I need the column K to reflect the date in column Y. As i already said this can be simply done by the formula

Please Login or Register  to view this content.

. But if you see my excel i have written all the formulas in VBA code and my main aim is to create the excel without any formulas in the sheet. I have done almost 99% but i couldnt write macros only for this case.

If you could help me on this it will be really great.

VBA Password: BULK123

**teylyn** · 01-29-2011, 03:05 AM

Hello,

if you use macros to manipulate protected sheets, then use code to unprotect the sheet before the data manipulation with the Worksheet.Unprotect method and apply the protection again before the end of the macro with the Worksheet.Protect method.

Or protect the sheet with code using the UserInterfaceOnly option set to true with Worksheet.Protect, so macros can change a protected sheet.