Search code examples
vbaexceluserform

VBA Excel : Adding a second set of commands if Textbox is changed

I am busy building a Shift rotation schedule using VBA and Excel at the moment I am sitting with a problem In my Userform I have 434 textboxes that give the shift allocation per agent as seen below: enter image description here

Now in order to get these colours to change I have a code in every Textbox (Named A1,A2.....A31 then B1, B2,,,,,,B31 etc.) the code goes as follows:

Private Sub A1_Change()
 If A1.Text = "A" Then
A1.BackColor = &H602000
 ElseIf A1.Text = "B" Then
A1.BackColor = &HC07000
  ElseIf A1.Text = "C" Then
A1.BackColor = &HEED7BD
  ElseIf A1.Text = "D" Then
A1.BackColor = &HF0B000
  ElseIf A1.Text = "W" Then
A1.BackColor = &HFF&
  ElseIf A1.Text = "M" Then
A1.BackColor = &H808080
  ElseIf A1.Text = "S" Then
A1.BackColor = &HA6A6A6
  ElseIf A1.Text = "P" Then
A1.BackColor = &H7D7DFF
  ElseIf A1.Text = "L" Then
A1.BackColor = &HD9D9D9
  End If

End Sub

I am trying now to allow the user to edit the shifts manually, Once this is done, they would be able to click on a set button that will copy the data from the Specific Agents row onto the worksheet based on the month selected for example:

Private Sub CommandButton2_Click()

If Sheets(3).Range("B5").Text = "2018-01-01" Then
Worksheets("LAYOUT").Activate
Sheets("LAYOUT").Range(B4).Text = A1.Value
Sheets("LAYOUT").Range(C4).Text = A2.Value
Sheets("LAYOUT").Range(D4).Text = A3.Value
Sheets("LAYOUT").Range(E4).Text = A4.Value
Sheets("LAYOUT").Range(F4).Text = A5.Value
.
.
.
.
Sheets("LAYOUT").Range(AD4).Text = A29.Value
Sheets("LAYOUT").Range(AE4).Text = A30.Value
Sheets("LAYOUT").Range(AF4).Text = A31.Value

ElseIf Sheets(3).Range("B5").Text = "2018-02-01" Then
Worksheets(1).Activate
Sheets("LAYOUT").Range(AG4).Text = A1.Value
.
.
.
.
.
Sheets("LAYOUT").Range(BJ4).Text = A30.Value
Sheets("LAYOUT").Range(BK4).Text = A31.Value

ElseIf Sheets(3).Range("B5").Text = "2018-03-01" Then
Worksheets(1).Activate
Sheets("LAYOUT").Range(BI4).Text = A1.Value
Sheets("LAYOUT").Range(BJ4).Text = A2.Value

ect

Now when I make a change and click on the CommandButton2 it does nothing... Where am I going wrong?

Solution

  • The best way to solve this issue is to start creating modules for each "action" that you want to do, for example to insure the colors in the textboxes make one Module and call it "Colour_Text" as an example is would look something like this 

    Public Sub Colour_Text1()
If PA1.Text = "S1" Then
PA1.BackColor = RGB(0, 32, 96)
PA1.ForeColor = RGB(255, 255, 255)
PA1.Font.Bold = True
ElseIf PA1.Text = "S2" Then
PA1.BackColor = RGB(0, 112, 192)
PA1.ForeColor = RGB(255, 255, 255)
PA1.Font.Bold = True
 ElseIf PA1.Text = "S3" Then
PA1.BackColor = RGB(189, 215, 238)
PA1.ForeColor = RGB(0, 0, 0)
PA1.Font.Bold = True
 ElseIf PA1.Text = "S4" Then
PA1.BackColor = RGB(0, 176, 240)
PA1.ForeColor = RGB(0, 0, 0)
PA1.Font.Bold = True
 ElseIf PA1.Text = "W" Then
PA1.BackColor = RGB(60, 60, 60)
PA1.ForeColor = RGB(255, 255, 255)
PA1.Font.Bold = True
 ElseIf PA1.Text = "P" Then
PA1.BackColor = RGB(166, 166, 166)
PA1.ForeColor = RGB(0, 0, 0)
PA1.Font.Bold = True
 ElseIf PA1.Text = "A" Then
PA1.BackColor = RGB(255, 0, 0)
PA1.ForeColor = RGB(0, 0, 0)
PA1.Font.Bold = True
 ElseIf PA1.Text = "S" Then
PA1.BackColor = RGB(169, 208, 142)
PA1.ForeColor = RGB(0, 0, 0)
PA1.Font.Bold = True
 ElseIf PA1.Text = "L" Then
PA1.BackColor = RGB(0, 176, 80)
PA1.ForeColor = RGB(0, 0, 0)
PA1.Font.Bold = True
 ElseIf PA1.Text = "F" Then
PA1.BackColor = RGB(112, 48, 160)
PA1.ForeColor = RGB(0, 0, 0)
PA1.Font.Bold = True
 ElseIf PA1.Text = "N" Then
PA1.BackColor = RGB(255, 125, 125)
PA1.ForeColor = RGB(0, 0, 0)
PA1.Font.Bold = True
 ElseIf PA1.Text = "UL" Then
PA1.BackColor = RGB(0, 176, 80)
PA1.ForeColor = RGB(169, 208, 142)
PA1.Font.Bold = True
 ElseIf PA1.Text = "US" Then
PA1.BackColor = RGB(169, 208, 142)
PA1.ForeColor = RGB(255, 0, 0)
PA1.Font.Bold = True
 ElseIf PA1.Text = "UN" Then
PA1.BackColor = RGB(255, 125, 125)
PA1.ForeColor = RGB(255, 0, 0)
PA1.Font.Bold = True
 ElseIf PA1.Text = "H" Then
PA1.BackColor = RGB(255, 192, 0)
PA1.ForeColor = RGB(255, 0, 0)
PA1.Font.Bold = True
End If
End Sub

    You then do the same for all the other text boxes, you can then call the module when you change the Text box like this:

    Private Sub PA1_Change()
Call Module1.Colour_Text1
End Sub

    This way you are only calling small changes thus freeing up your Memory :)