Hi there...
I'm new to this forum so forgive me if I'm doing something wrong here...
First of all I learned a lot from reading through posts on this forum, so thanks to all you pro's who are so kind to share expertise and help.
My knowledge in VBA code is very limited.
I can usually understand what it does when reading it but writing is horrible for me.
I have a question about a piece of code that's bugging me so I hope someone can help me...
In short:
I have a user form "FormTest1" on sheet 1, the info from this form's textboxes need to be saved to the dataset on another sheet called "CLT". So I need the code to check column A and search for a row with a specific number that matches a textbox on the user form, let's say TextBox1. Once found it can overwrite the entire row. The code I posted does the perfectly fine, however because this codes actually selects the "CLT" sheet before writing to it the screen gets all choppy. Especially considering that I have like 8 sheets that have similar functionality, the code runs but it's not even a little smooth. It get's the job done though...
I know there has to be a better way for this, preferably by not selecting the sheets it needs to write to. If someone would be able to improve this code for me that would be great!
Just so you all know I did not write this, its just snippets of code collected left an right and copy pasted together
Here the actual code:
Sub TestSearchAndReplace()
Dim lastrow As Long
Dim ABnum As Double
Dim ABrng As Range
Dim WriteRow As Long
Dim Ws As Worksheet
Set Ws = Worksheets("CLT")
With Ws
Sheets("CLT").Select '''''''THIS ONE IS THE PROBLEM I THINK BUT THE CODE DOESN'T WORK WITHOUT IT
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set ABrng = .Range("A1:A" & lastrow)
ABnum = FormTest1.TextBox1.Value
WriteRow = Application.Match(ABnum, ABrng, 0)
Cells(WriteRow, 1).Select
End With
Call ModuleUnprotectSheet.DisableSheetProtectionCLT
With ActiveCell
.Offset(0, 1).Value = EditClient_Form.TBFirstName.Value
End With
Call ModuleProtectSheet.EnableSheetProtectionCLT
End Sub
Any help would be much appreciated! Thanks in advance!
I'm new to this forum so forgive me if I'm doing something wrong here...
First of all I learned a lot from reading through posts on this forum, so thanks to all you pro's who are so kind to share expertise and help.
My knowledge in VBA code is very limited.
I can usually understand what it does when reading it but writing is horrible for me.
I have a question about a piece of code that's bugging me so I hope someone can help me...
In short:
I have a user form "FormTest1" on sheet 1, the info from this form's textboxes need to be saved to the dataset on another sheet called "CLT". So I need the code to check column A and search for a row with a specific number that matches a textbox on the user form, let's say TextBox1. Once found it can overwrite the entire row. The code I posted does the perfectly fine, however because this codes actually selects the "CLT" sheet before writing to it the screen gets all choppy. Especially considering that I have like 8 sheets that have similar functionality, the code runs but it's not even a little smooth. It get's the job done though...
I know there has to be a better way for this, preferably by not selecting the sheets it needs to write to. If someone would be able to improve this code for me that would be great!
Just so you all know I did not write this, its just snippets of code collected left an right and copy pasted together
Here the actual code:
Sub TestSearchAndReplace()
Dim lastrow As Long
Dim ABnum As Double
Dim ABrng As Range
Dim WriteRow As Long
Dim Ws As Worksheet
Set Ws = Worksheets("CLT")
With Ws
Sheets("CLT").Select '''''''THIS ONE IS THE PROBLEM I THINK BUT THE CODE DOESN'T WORK WITHOUT IT
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set ABrng = .Range("A1:A" & lastrow)
ABnum = FormTest1.TextBox1.Value
WriteRow = Application.Match(ABnum, ABrng, 0)
Cells(WriteRow, 1).Select
End With
Call ModuleUnprotectSheet.DisableSheetProtectionCLT
With ActiveCell
.Offset(0, 1).Value = EditClient_Form.TBFirstName.Value
End With
Call ModuleProtectSheet.EnableSheetProtectionCLT
End Sub
Any help would be much appreciated! Thanks in advance!