Posts by Justin Doward

Thankyou royUK for taking a shot at it, I think however I must be explaining things completely wrong so I will try again with an updated sheet.

In the file I would like to be able to update the cell range named "outcell" which would update the range "tableone" with the appropriate hyperlinks.

Dave Hawley showed me a VBA code using "case" however this would put an output into the "outcell" wheneever someone clicked on the range specified in the case. The hyperlinks that appear in the cells may be entirely different depending upon the search text entered (if it is found).

I think that the code (using "case") would do the job perfectly if I could see how to make the output vary depending what appears in a second cell. ie rather than have
when a4 is selected the outcell value is changed to "a", I would like

when a4 is selected the value from b4 is copied to the outcell (and this only occurs if the hyperlink is active (ie if(a4 <> "", copy(value(b4), outcell)), "").

that is obviously not code but hell I dont know any code.

Thanks again for the replies so far, I am learning as always.

named range

You can also have an indirect in the named range (so that you dont have to use the indirect in your formula, you simply enter the name of the range).

Control toolbox?

You can restrict the scroll area using the control toolbox. Select the control toolbox toolbar, selcect the "design mode" icon then the "properties" icon and set the scroll area.

The only problem is that you can only have one scroll area set per sheet using this (I think).

Justin

Hello all,

My apologies for the repeat thread but I cant seem to work this one out and am hoping you can help.

I have attached a workbook that is similar to the one I am working on and I want something along the lines shown in my rather sad looking code to happen.

the code below (adapted from Daves) does not even pretent to work but I am hoping you can see what I mean to do (I know nothing of VBA). I have put code tags around it and hope they work?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Select Case Target.Address


Case "$A$8"


if a8<> ""


Range("sheet1!b4").copy
Range("sheet2!a9").paste


Application.CutCopyMode = False


else nothing


case "$A$9"


if a9 <> ""


range("sheet1!b5").copy
range("sheet2!a9").paste


application.cutcopymode = false


else nothing


end sub

I guess I just need to work out a couple of things with the code...

for example why range("x").copy works as code but range("y").paste does not.

what do you use to make the macro activate only if there is a hyperlink showing (so that if you get a mad-clicker user you dont wind up with blank cells being copied)

and finally whether the hyperlink will continue as usual once the macro has run.

Thankyou for your time with this

Justin

Almost there me thinks

I think this is the idea that I want to happen, the workbook has had a couple of changes...

the code below (adapted from yours) does not even pretent to work but I am hoping you can see what I mean to do.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Address

Case "$A$8"

if a8<> ""

Range("sheet1!b4").copy
Range("sheet2!a9").paste

Application.CutCopyMode = False

else nothing

case "$A$9"

if a9 <> ""

range("sheet1!b5").copy
range("sheet2!a9").paste

application.cutcopymode = false

else nothing

end sub

I guess I just need to work out a couple of things with the code...

for example why range("x").copy works as code but range("y").paste does not.

what do you use to make the macro activate only if there is a hyperlink showing (so that if you get a mad-clicker user you dont wind up with blank cells being copied)

and finally whether the hyperlink will continue as usual once the macro has run.

Thankyou for your time with this

Justin

forgot to mention,

the text entered in the search cell has to be something from apples, oranges or pears in order for the hyperlinks to appear.

Justin

Fair enough, I was just re-reading what I had written myself and it made no sense to me,
Try this:

I am trying to work it so that when you click on any of the hyperlinks (sheet1 a8,a9 and a10) that appear after you have entered text in the search cell (sheet1 a1) the value of the linked-cell for the combobox will change (to a value a, b or c depending upon which of the hyperlinks you click.)

sorry for the confusion, hope this helps.

Again thankyou for the help

Justin

Hi Dave, thankyou for the reply.

I cannot get the code to work so I have put together an explanatory excel book to show what I am after.

In sheet1 I would like to change the value cell labelled "outcell" ... sheet2!a9 (which is the linked cell for the combobox), to change to the respective value from the sheet (this will update another page that uses this cell to fill its values).

Thankyou for the help

Justin

Hello all,

This site has proven to be an invaluable resource, thankyou to everyone who posts both questions and responses. This is the first time I have posted a question although I have had a bit of a look I cant guarantee it hasnt been asked before, if so please point me in the right direction.

I have developed a basic front end for a simple database using the vlookup and index search functions.

Within the front end I have an if function that shows a hyperlink if a search cell is used and a result is obtained

=IF(ISTEXT(Properties!X2), HYPERLINK(CONCATENATE("#", Properties!X2,"hazard"), Properties!Z2), ""))

I also have a combobox that you can select from.

1. Is it possible to have the combobox linked-cell changed to a particular cell (eg. x2) value when someone clicks on the hyperlink cell (only if the hyperlink is active). 2. Does it require VBA or can it be managed using a function?
3. will it update the combobox?

Thanks in advance

Justin

Hi Sandra,

As far as I can tell the formula wont work correctly as it is (you would have to use something like if(and((d2+(c2*d2))<=40, (d2-(c2*d2))<=40), etc...). All the brackets arent necessary but they can help ensure your order of operations is correct.

I think you are almost correct in your interpretation of what the formula does (should do), the if function works as follows
(if this is true (D2-+C2*D2<=40),
then do
this (D2-C2*D2),
else do
that (40))

so when someone gets a score less than or equal to forty they are given a calculated value, if they get over forty they are rounded down to forty. Seems like they dont like high achievers there?

Justin

Thankyou jindon

Just had a look at the formula you have used here and it is exactly what I am after, thankyou for posting it.

Very Elegant.