Posts by littlepete

why make an address list when they are all in my cell phone?

why try all those vba things if i can just look them up?

because i love to learn vba...

i found the solution :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)



Static PrevCell As Range



If Target.Cells.Count > 1 Then Exit Sub



If Not PrevCell Is Nothing Then



PrevCell.Font.Color = RGB(0, 0, 0)



PrevCell.Font.Bold = False



End If



Selection.Cells.Font.Color = RGB(255, 255, 255)



Target.Font.Bold = True



Set PrevCell = Target

i noticed that, but it works for the putting text in bold. so i'm not changing anything, trying to add yellow borders...

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:

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:

Sub opzoeken() ' - control p
Application.ScreenUpdating = False
' filteren op basis van persoon - eerste kolom
    Dim Title, prompt, Caption As String
    Dim geteld As Long
    Dim rngfound As Range
    Dim x As Variant
    Dim keuze As Integer
    filteruit
' vragen naar de gewenste persoon
    Title = "OPVRAGEN DATA"
    prompt = "DATA opvragen:" & Chr(10) & "__________" & Chr(10) & "[0] menu verlaten."
    Caption = ""
    gezocht = UCase(InputBox(prompt, "ADRESSENLIJST", Caption, 7000, 6000))
    bilboard
    If gezocht = "0" Then Exit Sub
    Selection.AutoFilter Field:=Range("rngpersoon").Cells(4, 1).Column, _
    Criteria1:="=" + "*" + gezocht + "*", Operator:=xlAnd
    Set rngfound = Cells.Find(gezocht, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False)
    If Not rngfound Is Nothing Then
        With ActiveSheet
            If ActiveSheet.FilterMode = True Then
                On Error GoTo 0
                Call markeer
            Else
                Set Rng = .AutoFilter.Range
                If Rng.Rows.Count > Rng.SpecialCells(xlCellTypeVisible).Rows.Count Then
                .ShowAllData
                End If
            End If
        End With
    Else
        With ActiveSheet
        If .AutoFilterMode Then
            Set Rng = .AutoFilter.Range
            If Rng.Rows.Count > Rng.SpecialCells(xlCellTypeVisible).Rows.Count Then
                .ShowAllData
            End If
        End If
        End With
    End If
zoombreedte
sortalfa
Application.ScreenUpdating = True
tooneersterij
End Sub

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

Option Explicit
Dim r As Range
Private Sub Worksheet_Activate()
Set r = ActiveCell
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("gegevens").Select
If Target.CountLarge > 1 Then Exit Sub
With r
 .Font.Bold = False
 .Font.Color = RGB(255, 255, 255)
End With
With Target
.Font.Bold = True
.Font.Color = RGB(255, 255, 0)
End With
Set r = Target

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... :

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 !!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Static PrevCell As Range

If Not PrevCell Is Nothing Then

PrevCell.Font.Bold = False

Target.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone

End If

Target.Font.Bold = True

PrevCell.BorderAround ColorIndex:=5, Weight:=xlThick

Set PrevCell = Target

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

can someone explain to me the effect from that event to highlight the active cell making my macro to add and fill new row

doesnt work anymore? ...

that's exact as i thought: tables wont help me...

at this moment i'm exploring solutions for two problems:

1. a link between the name of mom and dad in a person's row to the row of mom or dad;

and 2. highlight the active cell (text in bold or thick borders for ex.)

thx for the explanation

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 :

Private Sub Worksheet_Activate()
Set r = ActiveCell
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
With r
 .Font.Bold = False
End With
With Target
.Font.Bold = True
End With
Set r = Target

(...)

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

in that vba this is the 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 ?

Public Function naam(celnaam As Range)
Dim ftitel As String
Dim fvoorn As String
Dim fnaam As String
Dim fbijn As String
Dim ftrscr As String
ftitel = Offset(celnaam, 0, 1) & " "
fvoorn = propercase(Offset(celnaam, 0, 2)) & " "
fnaam = UCase(Offset(celnaam, 0, 4)) & " "
fbijn = " '" & Offset(celnaam, 0, 3) & "' "
ftrscr = "[ " & Offset(celnaam, 0, 5) & " ] "
If Offset(celnaam, 0, 5) <> 0 Then
naam = ftitel & fvoorn & fbijn & fnaam
Else
naam = ftitel & fvoorn & fbijn & fnaam & ftrscr
End If
End Function

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

i know !!! that's why i'm asking how to make a function that converts my name to MY NAME or My Name !!!

thank you and have a great day !

hello

I'm already using named ranges, but i still need to fix those, because they need to adjust when rows are added...

I'm gonna try to cut the formula in two parts that will no doubt help, i still wanted to know how to make such a formula !

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 !!!

i cannot edit my post, so an addendum:

to give you an example:

my data: row 2. my mother's data: row 144. my father's data: row 171.

column: B: first and last name. column ax: mother's name. column ay: father's name.

so:

cell ax2: link to my mother in cell B144; cell ay2: link to my father in cell B171.

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 !

hello

i did try renaming but it said there was a double name... and deleting combobox1 didn't work either...

now i'm trying to put an inputbox together to add new data !

thank you !