CODEWITHSUNDEEP
excelvba
charliealpha
charliealpha

Reputation: 307

copy paste link to user-input cell reference

I make a lot of data entry mistakes and I am trying to come up with a way to verify my input. I copy values from cells to another, I figure it would be best if I linked to the cell directly and then had those cells automatically colored.

Here is my proposal:

Selection.Copy
Selection.Interior.ColorIndex = 37

Set rng = Application.InputBox("Cell reference", Type:=8)

Now I cannot figure out a way to paste the links to the input cell reference. It seems like by selecting a cell with the input box, the selection is lost.

Upvotes: 0

Views: 324

Answers (2)

Felipe
Felipe

Reputation: 163

So, you want to select a cell and change its contents based on another cell contents, right? You are creating a reference to the source cell by using the set statement. Now, you just have to use the .address property of your range to get a string value that represents the range reference in the language of the macro (See help for this property).

Option Explicit
Sub CopyingCellContents()

Dim rng As Range

Selection.Copy
Selection.Interior.ColorIndex = 37

Set rng = Application.InputBox("Cell reference", Type:=8)
Selection.Value = activesheet.range(rng.Address)

End Sub

A tip: ALWAYS set the Require Variable Declaration in your code.

Considering your further explanation and your own code, I tried to update yours.

Sub xxx

Dim rng As Range
Dim inp As Range
Dim Sh as worksheet 'Worksheet where your range is.

set Sh= workbooks("Name Of The Workbook").worksheets("Name Of The Worksheet")

Set inp = Selection
inp.Interior.ColorIndex = 37

Set rng = Application.InputBox("Copy to", Type:=8)

sh.activate
inp.Copy Destination:=rng, Link:=True

End sub

Change "Name Of The Workbook" and "Name Of The Worksheet" by the names of the workbook and and worksheet respectively where the ranges you want to manipulate are. Don't forget to use "".

Upvotes: 1

charliealpha
charliealpha

Reputation: 307

I have reworked some code. Here it is :

Dim rng As Range
Dim inp As Range


Selection.Interior.ColorIndex = 37
Set inp = Selection

Set rng = Application.InputBox("Copy to", Type:=8)
inp.Copy

rng.Select

ActiveSheet.Paste Link:=True

It seems to work, thanks!

Upvotes: 0

Related Questions