Posts by littlepete
-
-
i found the solution :
Code
Display MorePrivate 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
-
-
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:
CodeStatic 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:
Code
Display MoreSub 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):
Code
Display MoreOption 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... :
-
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 !!!
Code
Display MorePrivate 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
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 :
Code
Display MorePrivate 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 :
CodeRange("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 ?
-
Code
Display MorePublic 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 !!!
-
-
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 !