Attach an example of your workbook.
Also, explain what the purpose of your code is. If it's data entry then there's probably a better way.
Posts by royUK
-
-
You can only have one Worksheet_Change event in a worksheet. Have you got both in the same worksheet?
-
I would suggest that you simply need Find & Replace manually or in VBA. It's hard to say without seeing an example of the worksheet.
I've edited your last post to make it readable, I don't know why it wasn't before.
-
I found this some time ago, but I can't locate where now. I've not tried it
Display MoreCode- Function GetVideoDuration(fPName As String) As Long
- 'returns the duration in seconds of the specified video path+fPNamename
- Dim oFolder, ofPName
- With CreateObject("Shell.Application")
- Set oFolder = .Namespace(Left(fPName, InStrRev(fPName, "\") - 1))
- Set ofPName = oFolder.ParseName(Right(fPName, Len(fPName) - InStrRev(fPName, "\")))
- GetVideoDuration = GetVideoDuration = oFolder.GetDetailsOf(ofPName, 27)
- End With
- End Function
- Sub Test()
- MsgBox GetVideoDuration("Path To Video Here\myVideo.mp4") ''///returns a duration in seconds
- End Sub
-
Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post
All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.
How to use code tags
Just highlight all of the code and press the <> in the post menu above button to add the code tags.
Thanks.
Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you!
Read this to understand why we ask you to do this
https://www.excelguru.ca/content.php?184
It's good that received a solution. To close this thread, please either add your answer and accept it as an answer: this could help others or you might get a better answer.
Three rules broken, not a good start!!. READ THE FORUM RULES BEFORE POSTING AGAIN - you will find that all Excel Forums have similar rules.
-
Why do you need a TextBox or even VBA? You can simply use Find and Replace.
-
Which event, when a cell in column H changes?
Then you have to also allow for the AB column being changed
-
What will trigger the macro to run?
-
What boxes will be checked?
-
What do you mean?
Add a Note if the cell contains data.
Remove all Notes if AB9 contains Tes.
-
-
You cannot have a checkbox in Notes
-
Do you mean Cell Comments now called Notes?
Attach an example workbook
-
The fastest way ts to utilise AutoFilter
Display MoreCode- Sub deleteFilteredData()
- Dim rDelete As Range
- Dim lCalc As Long
- Dim sDelete As String
- With Application
- lCalc = .Calculation
- .Calculation = xlCalculationManual
- .ScreenUpdating = False
- On Error GoTo exit_proc
- sDelete = "Record Only"
- 'Sheet with the data, change the name
- With Sheet1
- .AutoFilterMode = False
- 'Apply the filter, this range of data starts in A1
- .Cells(1, 1).CurrentRegion.AutoFilter Field:=4, Criteria1:=sDelete
- With .AutoFilter.Range
- On Error Resume Next
- Set rDelete = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
- .SpecialCells(xlCellTypeVisible)
- ' On Error GoTo 0
- If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
- End With
- 'Remove the AutoFilter
- .AutoFilterMode = False
- End With
- exit_proc:
- .ScreenUpdating = True
- .Calculation = lCalc
- End With
- End Sub
-
This is a working example. You must prepare the cells correctly before running, this is true when protecting sheets with or without VBA.
-
You must be doing something wrong. Attach the workbook.
-
Have you set up protection for the sheet correctly before running the macro? If you don't unlock all the cells first then they are locked by default.
-
You might find this a little better, especially the error handlers to prevent events remaining off in case of an error, events will not reset automatically.
Display MoreCode- Sub Button1_Click()
- Dim OutApp As Object, OutMail As Object
- Dim cell As Range, FileCell As Range, rng As Range
- With Application
- .EnableEvents = False
- .ScreenUpdating = False
- With Sheets("Sheet 1")
- Set OutApp = CreateObject("Outlook.Application")
- ''/// SpecialCells will raise an error if no cells matching the specified Type is found
- On Error Resume Next
- For Each cell In .Columns("B").Cells.SpecialCells(xlCellTypeConstants)
- On Error GoTo 0
- 'Enter the path/file names in the C:Z column in each row
- Set rng = .Cells(cell.Row, 1).Range("C1:Z1")
- If cell.Value Like "?*@?*.?*" And _
- Application.WorksheetFunction.CountA(rng) > 0 Then
- Set OutMail = OutApp.CreateItem(0)
- With OutMail
- .to = cell.Value
- .CC = "Coworker Email Here"
- .Subject = cell.Offset(0, 2).Value & "-Company Verbiage here...-" & cell.Offset(0, -1).Value
- .Body = "Company Verbiage here..."
- For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
- If Trim(FileCell) <> "" Then
- If Dir(FileCell.Value) <> "" Then
- .Attachments.Add FileCell.Value
- End If
- End If
- Next FileCell
- If .Attachments.Count > 0 Then
- .send
- Else
- .Display 1
- End If
- End With
- Set OutMail = Nothing
- End If
- Next cell
- End With
- err_handler:
- Set OutApp = Nothing
- .EnableEvents = True
- ''/// not needed now
- ' .ScreenUpdating = True
- End With
- End Sub
-
Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post
All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.
How to use code tags
Just highlight all of the code and press the <> in the post menu above button to add the code tags.
Thanks.
-
What do you think the code is doing? This code actually does nothing but check if a cell is empty.
The code loops through the cells and if it finds a cell that isn't blank it locks it unprotects the sheet , locks the cell then protects the sheet. That is unnecessary. What is expected to be in cells - formulas, numbers or text? or text
This will find and lock all cells containing text in the range.
Display MoreCode