Posts by littlepete

    hello ;)


    i tried your code but the borders stay where they are... that is something i achieved already too... so far i use this,

    to make the text go bold and normal on next click:


    Code
    Static PrevCell As Range
    If Target.Cells.Count > 1 Then Exit Sub
    If Not PrevCell Is Nothing Then
    PrevCell.Font.Bold = False
    End If
    Target.Font.Bold = True
    Set PrevCell = Target


    i dont find a way to to add borders, i tried borderaround, and borders but they never disappear after the next click...

    thanks for your help :) !!!

    hello ;)


    I certainly do no ignore people... I am working (having fun) in excel every day and I have more then one item i'm trying to construct.


    sorry that i'm not going for tables but they do seem to changes too much, dont forget i'm just a basic, all that i achieve is not by knowing but by finding and trying...


    what questions do you have ?


    attached the royalty version of my file, feel free to try :) ! and thank you for your time !adressen 21 royal.xlsb

    hello ;) these are the pieces that work separately :


    1. vba to search column for a name:


    and secondly, event vba to put text in bold and add yellow thick border around activecell, only when active ( in event selection change):


    they both work when the other is deactivated (i put ' before the line to make them a remark) ...


    i now only use the event activecell with bold text that works perfectly together with the rest... :


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static PrevCell As Range
    If Not PrevCell Is Nothing Then
    PrevCell.Font.Bold = False
    End If
    Target.Font.Bold = True
    Set PrevCell = Target

    hello :)


    my selection change event changes the active cell to bold text that returns to normal text when clicked away.

    the same should happen for going from no borders to thick yellow borders:

    they appear, but after the next click they stay on the previous cell...


    what is my mistake ?


    have a great sunday !!!



    hello :)


    in my quest to create an unbeatable link between person and parents, i'm thinking to use instr with substring and string:

    it could work like this :


    i click on a cell starting a macro.

    the active row is the person for whom we search the mother. (forex. mine: row 2)

    we copy the value of column CP in the active row that contains the name

    we search column B to find the name as a double string (column M & column N)

    the macro ends with selecting that cell in column B ... (forex. in row 144 my mother)


    who can fix that ...


    thanks for the help !!!

    hello ;)


    i did try already twice to convert to a table but nothing works when i do that... the first thing i try are macros...

    all macros i try give errors, and being a basic user i have no idea where and what i should have to change in vba...


    of course when you do look up convert to table i see all good things, but nowhere i read about things you lose...


    so thank you but for now no table...

    hello :)


    I love learning vba there are so many ways to be creative...

    but one of the most annoying things is this: you succeeded in creating a good thing, so you start trying the next creation...

    and when that's done...? the creation before is not working anymore :( ...


    what did i do?


    I created vba to copy the last filled in row and add it as new last row, deleting all data except formula. after that

    i could go through all cells in that row and fill in all new data.

    it worked perfectly !!!


    next goal: highlight the active cell with borders or background color... they worked too slow, so i just make text of the activecell bold

    and when clicking the next cell, the bold disappears...

    it worked perfectly too !!!


    but i notice now that my adding new row vba is stopping...


    to make the activecell highlight in bold, i used :



    is the worksheet activate event the reason of my adding new row vba not working,


    in that vba this is the code :


    Code
    Range("A" & nieuwerij - 1 & ":CQ" & nieuwerij - 1).Select
        Selection.Copy
        Range("A" & nieuwerij).Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
            , SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Range("A" & nieuwerij & ":CQ" & nieuwerij).SpecialCells(xlCellTypeConstants).ClearContents


    the error appears on line 4 : "selection.pastespecial paste ...


    what has changed that i don't understand ?



    Hello :)



    I am trying to make a function to turn first name, last name, title, ... into one "name" function...

    (trscr = needed if the name is for.ex. in arab to write the transcription.


    it gives me a name? error...


    thank you for your help !!!

    Hello :)


    fact: my formula is too long: over 8192..

    i already converted my excel to a bin file, but still it refuses to accept my longer formula.


    can i make a custom (two letter) function to replace the original function (with a long name) ?


    i would like to replace hoofdletters(B1) with hl(b1) (hoofdletters is dutch for uppercase)


    what would that look like? i know i need a public function ... end function in the workbook place... and what looks the function like then?


    thank you !!!

    Hello :)


    My extended address list, containing all possible data of the people i know does to my happiness all the things i like !!!


    column M first name and column N last name for each person; and column Ax for the mother and column Ay for the father.


    the cells in columns ax and ay, when containing a name, are made a hyperlink so that when clicked the row of the mother/father is selected.


    that works perfectly !!! ... until rows are added of course.

    I have tried to change the range in de named range list from $B$144 to B144 but that is corrected automatically. And thus the row numbers

    do not adjust when rows are added.


    How can i change the named range formula " =gegevens!$B$34 " to a formula that will adjust ?

    or is there another way to make clicking on the mother's/father's cell name jumping to their row ?


    curious if there is a solution !!!


    and thank you for your time :) !!!

    hello :)



    I do love vba a lot, and really a lot, but so many things are just too complicated...

    but this is one of the simpliest and still i'm not advancing.


    In my inputbox i want the sentence "filling in name", "filling in first name" and so on, depending on which column (K, L, ...) i'm putting new data.


    but he refuses all..

    the line If Cells(4 & dezekolom).HasFormula = True Then works well...

    these lines too:

    With Worksheets("gegevens")

    Cells(nieuwerij, dezekolom).Value = keuze

    End With


    then why is it not working to make the inputbox:

    dezetitel = Range(dezekolom & 4).Value OR dezetitel = cells(4, dezekolom).value ----- (both dont work)

    keuze = InputBox(Chr(10) & _

    "Deze lijst bevat " & aantalcontacten & " contacten." & Chr(10) & Chr(10) & _

    dezetitel & " invullen: ... " & _

    Chr(10) & Chr(10) & _

    "tik hier de nieuwe data in.", "NIEUWE DATA...", 10000, 5000)


    (keuze=choice ; second line says how many rows there are ; and after that this title & fill in : ... (which does not work)


    what is the solution...?


    thank you and have a great day !