Edit multiple sheets from a userform combobox at a time if conditions are met

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
So, my goal is to edit all three sheets at a time, for the items in a given column. There are three sheets in all that are loaded into the combobox. “Round 1”, “Round 2”, “Round 3”. Each time I run the code below, there is one of the sheets active in the combobox. I have this variable called, MyEnterValue, which is loaded when I click my listbox – used this trick because using the actual enter value was creating issues especially after the original value has been altered and I enter it again. Using the listbox click event seemed to be the coolest way I came about. So when I run this code to edit the current selected sheet, I will check if MyEnterValue is not equal to the value in txt2. So when they are not equal, then I loop through column C of the other two sheets, which I don’t know how best to identify them ATM, to see if there is an item in each of them that matches the MyEnterValue variable content. If there is, then I update that value with the content of the txt2 textbox. I am stacked with the coolest logic and I need tech support. Thanks for your time and help in advance.

Code:
Sub EditAllSheets()
Dim MyRound$, findvalue As Range, i&
MyRound = Me.ComboBox1.Text
With Sheets(MyRound)
Set findvalue = .[B7:B107].Find(what:=txt1.Text, LookIn:=xlValues, lookat:=xlWhole)
 For i = 1 To 5
      findvalue.Offset(, i) = Trim(Controls("txt" & i + 1).Value)
 Next i
End With
End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
This is how I came close to fixing my challenge, now what I need is someone telling me if there are downsides with this procedure and how to fix that if possible. Also, if there are still ways to optimize it for fast running

Code:
Select Case ComboBox1.ListIndex
        Case 0
                 With Sheets("Round 2")
                         For Each eName In .[C7:C107]
                              If eName  = MyEnterValue Then
                                   eID = eName.OffSet(, -1)
                                   eID.OffSet(, 1) = Txt2.Text
                                   Exit For 
                               End If
                        Next 
                End with

          'I repeat the loop above for Round 3 sheet
        Case 1
                'Repeat the codes above and adjust

        Case 2
                 'Repeat the codes as before
End Select
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top