Posts by Justin Doward

    second column?


    The only thing I found was if you make a second column of =value(a2) and drag down. then use this column to copy the values back to the respective cells using pastespecial.

    I have a number of range names that I hyperlink to but when you get to the area the highlight makes the colours look a bit of a mess.


    Not a particularly important point I guess, just irritating. Is there a way to avoid the highlight while maintaining the hyperlink.


    I can hyperlink to a smaller region but then there is no guarantee that all of the page I am linking to will be displayed (depends upon the last user scroll on that page).

    Ok I seem to be going around in circles, I have tried numerous ways of getting this to work and it just wont. All I need to work out is how to have a value from one page copied to another when a cell is selected (I will try and incorporate a hyperlink into the code as well)



    I recorded the code above (between "case"$A$5"" and "end select") but it reaches the first range select and drops out with "select method of range class failed" even though the code works if run as a macro.


    I have also tried with worksheets("worksheetname") the copying the range but that also will not work for me.


    Please help.

    Post the example


    Hi w00t, I think you need to be a little more clear with your question and try posting an example of what you are after... it is difficult to interpret what you want from that question

    Have you recorded macros?


    Hi radner,


    I have only just started to look at Macros and so far I have found the best way to start learning is to record a sequence of events (changing sheets, copy, paste, scroll etc....) and then go and have a look at the code that gets written automatically.


    as for your table macros, I cant help you with the macros but I can suggest that you instead use some vlookup, search and index functions with a search cell as a reference. Also start using named ranges (use insert/name/define from the drop down menus)


    I have set up a search function using your sheet, have a look at the functions on the datasheet (tacked on the end of your table). You also generally need a unique identifier for each record in the table ... I have just used numbers in this one.


    enter "amex" or "bofa" in the search text cell (A1) on the search sheet and it will ouput those records. Let me know if you want more detail on what the functions are doing.


    Justin

    Files

    • mymacro.xls

      (28.16 kB, downloaded 19 times, last: )

    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.

    Files

    • outline-1.xls

      (33.28 kB, downloaded 24 times, last: )

    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?



    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

    Files

    • outline.xls

      (29.7 kB, downloaded 31 times, last: )

    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

    Files

    • outline.xls

      (29.7 kB, downloaded 79 times, last: )

    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

    Files

    • outline.xls

      (26.62 kB, downloaded 80 times, last: )

    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