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

Question

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:

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?

Why make it complicated? Why not create the same in an excel worsheet and use conditional formatting? — Siddharth Rout
– Siddharth Rout, Commented Nov 29, 2017 at 12:58
The problem is that the shifts are based on an algorithm on the excel sheet. and we do not want to give the users access to the worksheets. — Flaniganga
– Flaniganga, Commented Nov 29, 2017 at 13:02
You do not have to give access to relevant worksheet. Hide them and protect your file. Create a new sheet for this. Just give access to that sheet — Siddharth Rout
– Siddharth Rout, Commented Nov 29, 2017 at 13:10
This is a lot of time invested for a task that can be solved in a more simple way. Read something about protect worksheets. — user8753746
– user8753746, Commented Nov 29, 2017 at 13:17
If you still want to go the "Complicated way" then what you need is Control Arrays — Siddharth Rout
– Siddharth Rout, Commented Nov 29, 2017 at 13:20

Community · Accepted Answer · 2018-05-08 21:49:49Z

1

Wow, that's... um... really something. You get an 'A' for for Determination, but a "C-" for Study Skills. (I mean that in the kindest way possible!) :)

There are a lot of ways to create a dynamic multicolored form like this (with no sensitive code available to the users) and you pretty much picked the hardest and most complicated way. Unfortunately, complicating simple tasks tends to make them more likely to break in the future for a small reason, and then it can take forever to figure out the problem, if you're baby doesn't crash altogether, losing all your data.

I don't think I've ever seen a Too Many Variables error before! (Even Excel wants you to simplify.) Sorry if this doesn't qualify as an answer, but I think you're best best it to start over with your formatting in a proper way.

omg, "5208 lines of code left") IF you know exactly how many lines of code you have left, you are being way too repetitive! The whole point of Excel, or VBA, or coding in general, is make the computer do the work!

If you're concerned about learning new Excel features, don't be. You obviously have some skill & organization to have made it as far as you did on that! There are some basic things you should teach yourself in Excel...

Some things to learn, ASAP (you will be glad you did!)

Select..Case statements (instead of ElseIf ElseIf ElseIf)
With..End With statements (instead of A1.BackColor A1.BackColor A1.BackColor)
VLookup (store reusable values in tables)
Match / Index
Protecting Worksheets in Excel
CONDITIONAL FORMATTING! _{(Automatically change a cell's color etc based on a value or a formula.)}
Arrays! Both for storage (like color names and cell trigger values) and for control.
VBA Events! (Make stuff happen automatically when other stuff happens) --from the website of Chip Pearson (the king of Excel)
Making a static web page of an Excel Page
Some of Excel's amazing built-in features
Microsoft Excel formulas and features that you need to know
ExcelGuru Forums
and even: Rotating Shift Schedule Templates for Excel (that are ready to use, free, you can adapt as you need, built by professionals)

Good luck... Those are some nice color choices!

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Nov 29, 2017 at 13:39

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Flaniganga Over a year ago

I have decided to go a different route.. Instead of trying to compile a two scenarios to do the work I have created another button to handle the transfer for example

ashleedawg Over a year ago

Whatever works. The beautiful (or sometimes frustrating) thing about Excel and VBA is that there can be many different ways to accomplish the exact same task. I still recommend checking out the links, especially Chip Pearson's 600+ copy-and-paste solutions; there's something there to benefit every Excel project and boost your skill level along the way.

Flaniganga · Accepted Answer · 2018-02-22 11:59:48Z

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 :)

Collectives™ on Stack Overflow

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

2 Answers 2

Some things to learn, ASAP (you will be glad you did!)

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Some things to learn, ASAP (you will be glad you did!)

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related