VBA Private Sub for all sheets

[BrainDiseasee]

Hello,

I have a private sub i am using in a template style sheet, goal is to be able to essentially duplicate this "Template" worksheet and apply unique title and information to the new sheet name. I have all of that down.
Here is my problem i have two TextBox's from the developer tab each one has a private sub in the sheet to display the data entered into a cell on another sheet called "Master_Data". Here is the code, i am needing a solution on how to have this apply to each sheet or if i can do something like "If ws.Name <>" to limit what sheets dont need this. Anyway below is the code and help would awesome, thanks!!!!

Option Explicit

Private Sub TextBox1_Change()
Sheets("Master_Data").Range("W9").Value = TextBox1.Value
End Sub

Private Sub TextBox2_Change()
Sheets("Master_Data").Range("W10").Value = TextBox2.Value
End Sub

 

[Bosquedeguate]

It is not clear from your description exactly what you need. Your code appears to be OK.

What do you want to be applied "to each sheet"?
What value(s) do you have in your text box(es)? Where should these values be placed in the Master_Data sheet? (your example is putting them in W9 and W10)
What does column W and rows 9 and 10 represent? The worksheets that need some value?
Please provide more details.

 

[BrainDiseasee]

It is not clear from your description exactly what you need. Your code appears to be OK.

What do you want to be applied "to each sheet"?
What value(s) do you have in your text box(es)? Where should these values be placed in the Master_Data sheet? (your example is putting them in W9 and W10)
What does column W and rows 9 and 10 represent? The worksheets that need some value?
Please provide more details.

Hi

Thanks for you response. The Private sub code i have mentioned is with in one worksheet, i need to have a macro or something that can apply this code to every worksheet in the workbook.
The TextBox1 i am entering in values of either "Medium, Large ,X-Large" TextBox2 is just any date in MM/DD/YYYY format.
Range W9 & W10 from the "Master_Data" sheet is taking the values from either textbox1 or textbox2, another cell is performing a calculation based on these values.

TextBox1 & TextBox2 Master_Data Ranges W9:W10

 

[Bosquedeguate]

• Create a Module in your VBA code.
• Copy the code from your "Private Sub" in your worksheet to a similar Sub with an appropriate name.
• Modify your code to apply to all the worksheets you want to update, for example

Note this is just some sample code since I have no idea what data you want to be copied to other worksheets. However, the basic loop should get you started.

Option Explicit
Public Sub UpdateWorksheets()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    If ws.Name <> "Master Data" Then 'or any other sheets you want to exclude
      ws.Range("A1") = Worksheets("Master Data").Range("$W$9") 'value from another worksheet
    End
  Next ws
End Sub

 

[BrainDiseasee]

Hello,

This looks like it will be the revers of what i need. This code will apply my value from W9 to A1 on each ws. What i need is each ws that has the object "TextBox1" for the text from that object to be in cell W9 on the master data sheet. Regardless of how many sheets only one text box will be tied to the cell at a time this is working in a separate model. I am able to manually add the private sub to each sheet and the code works but i need a way to automate that private sub to be applied to each sheet.

 

[Bosquedeguate]

Hello,

This looks like it will be the revers of what i need. This code will apply my value from W9 to A1 on each ws. What i need is each ws that has the object "TextBox1" for the text from that object to be in cell W9 on the master data sheet. Regardless of how many sheets only one text box will be tied to the cell at a time this is working in a separate model. I am able to manually add the private sub to each sheet and the code works but i need a way to automate that private sub to be applied to each sheet.

Are you always entering values in the two text boxes? You might consider creating a user form with text boxes (maybe a ComboBox for the sizes: Medium, Large, X-Large) and maybe a date picker control for the date you are entering. Updating the Master_Data sheet from the form can be done via a "Submit" button or the Control Source property of each control could be linked to the Master_Data sheet cells. This way as you add sheets - the form would not need to change or be updated.
The form can be displayed from the worksheets and populate the cells on the Master_Data sheet.

I am still not fully understanding what you need; I think that is part of the problem.

 

[BrainDiseasee]

I have multiple sheets that have these text boxes, all pointing to the same cells w9 & w10 on same sheet Master_data sheet. Each sheet is requiring the "private sub Sheets("Master_Data").Range("W9").Value = TextBox1.Value" i am trying to apply this private sub to each worksheet

 

[Bosquedeguate]

The only suggestion that I have is that you put your code for TextBox1 and TextBox2 events into a Module Sub
and make the Sub Public

Something like the following
You will still need event handler code for your TextBoxes on each Worksheet

Private Sub TextBox1_Change()
  UpdateWSMasterData Me.TextBox1
End Sub

Private Sub TextBox2_Change()
  UpdateWSMasterData Me.TextBox2
End Sub

Public Sub UpdateWSMasterData(tb As MSForms.TextBox)
  Dim i As Single
  Dim addr
  i = Val(Right(tb.Name, 1))
  addr = Choose(i, "W9", "W10")
  Sheets("Master_Data").Range(addr).Value = tb.Value
End Sub

Does your Master_Data worksheet have a Cell that identifies which worksheet updated cells W9 and W10 last? If it doesn't you might consider adding that value

Then you could create a sub/event handler that writes the Start By date in the Target worksheet that provided the W9 and W10 info.

 

[BrainDiseasee]

Is there a way to share a screen recording on here to show what i am doing?

 

[Bosquedeguate]

Is there a way to share a screen recording on here to show what i am doing?

Not that I know of