• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA script

Raja Kamarthi

New Member
Need a VBA script to replace a particular blank cell with an active cell value for e g in the spreadsheet cell A1 is blank and when I select cell B1 which has XYZ value, this value should be reflecting in cell A1 and in the same way if I select cell C1 which has “123” value then the same should reflect in cell A1. Please share your inputs


Any help would be highly appreciated
 
Hi Raja,


Welcome to this forum.


I assume you have data in sheet1


Press ALT+F11 from keyboard to open the VB editor. Double on sheet1 to bring the sheet1 code module and copy paste the below code, save it.

[pre]
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Range("A1").Value = ActiveCell.Value

End Sub
[/pre]

This code will do the following:


Say you have data at any cell (except A1), double click on that cell. Macro will take that value (the cell where you have double clicked, and place it in A1.


Hope this helps.


Kaushik
 
@Kaushik: Thank you so much for your time and input. Your code works like a charm.However I still seek your help as I am facing a glitch.

Since my sheet is filled up with formulaes, when I double click on any of the cell, the cell goes into "EDIT" mode and the formulae range is displayed.What I need is a single click instead of double click. Could you please help me out?


Thanks

Raja
 
@Raj Kamarthi


Hi


You can use the below code it will display when you select the cells


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("a1").Value = ActiveCell.Value

End Sub


Hope it will clear


Thanks


SP
 
@ sgmpatnaik


This is absolutely awesome bro,many many thanks for this piece.


Your code does the magic I was looking for.


Once again, Thanks a ton


Regards,

Raja
 
@Raja Kamarthi


Glad i solve your problem


and thanks for your kind words


welcome back if any problem


cheers


Thanks


SP
 
@ sgmpatnaik


Your solution has indeed helped me but now I'm facing a new problem.


Since my sheet is filled with formulaes and I need to protect the sheet but when I do this, the code is throwing an error


Is there a way to over come this?


Thanks

Raja Kamarthi
 
@Raja Kamarthi


Hi


When we are going to protect the sheet then we can't edit the text from that a cell when we are use the allow range to edit option and we mention some range then we can edit that particular shell only


so please give the allow range as A1 then Check


other wise download this file and check


https://dl.dropbox.com/u/75654703/Book1.xls


password is 1


Thanks


SP
 
@sgmpatnaik:


Heartfelt appreciations for your efforts, bro.


But somehow I am unable to get this "Allow Range to edit" option. Let me share the procedure of how I protect a sheet:


1) Select complete sheet (Alt + A)

2) Format cell-Protection-Uncheck Locked cells

3) Select cells which are to be protected, then format cells-protection-check Locked cells

4) Right click at the sheet name and select protect sheet and give password


This is how I proctect the sheet and no where I find this "Allow Range" option


Please help me out


Thanks

Raja
 
@Raja


to protect a sheet you don't need to select all because when we protect sheet means it's protect to entire sheet


please follow the instruction to protect the sheet


1. Select the Review Button


2. there you can find the protect sheet, Protect Book and more


3. Select the Allow users to edit Range


4. Now a Dialog box will appear


5. Click on the new button and give the A1 in the Refers to Cell Box and Click Ok a button and Apply and Select Ok


6. Now Select the Protect Sheet and give the Password


Hope it will clear


Thanks


SP
 
@SP

I really dont know how to convey my Thanks for your timely help.


You have been very supportive and patient in resolving my query.


Thank you very much, bro, I wouldnt be able to complete my task without your inputs


Regards,

Raja
 
Back
Top