OK, I understand you are starting to learn VBA. It helps to get some order in your workIn the VBA editor go to the menu Tools/Options...
On the Editor tab ensure that the 'Require Variable Declaration' is ticked. (Basically everything here should be ticked to make your life easy)
What this does is when you open a new module at the top it will auto insert:
For your existing modules copy 'Option Explicit' to the very top line.
Now the editor will complain every time you try to run a subroutine where a variable hasn't been declared. This will ensure that typos are caught.
the indent your code.
Only your
Sub mysub()
End Sub
are left alligned. The rest is at least indented once.
Within every loop or if section an extra indentation is required.
This make error finding so much easier.
Then you have a lot of empty subs in your code. Delete them for clarity.
Also separate your subs with some empty lines to make it more readable. Your code should look something like:
Code:
Private Sub ClearButton_Click()
Worksheets("AuditForm").Activate
Range("H11:L15,H17:L21,H23:L27,H29:L33,H35:L39").Select
Selection.ClearContents
ComboBox1.Activate
End Sub
Private Sub UpdateButton_Click()
Dim rw As Integer
Dim col As Integer
Dim currentCell As Range
Set currentCell = Worksheets("5-S Assessment Charts").Range("B13")
While currentCell <> ComboBox1.Text
Set currentCell = currentCell.Offset(0, 1)
Wend
rw = currentCell.Row + 1
col = currentCell.Column
Worksheets("5-S Assessment Charts").Cells(rw, col) = Worksheets("AuditForm").Range("Total").Value
Set currentCell = Worksheets("5-S Assessment Charts").Range("A20")
End Sub
Which is exactly the same as what you had above. But far easier to read.
All this and I haven't even answered your question.
OK the answer to your riddle lies most likely in the secon last line of your code.
(The last line of your code doesn't do anything usefull, more about that later)
Code:
Worksheets("5-S Assessment Charts").Cells(rw, col) = _
Worksheets("AuditForm").Range("Total").Value
Before this you have set the currentCell to the cell where the heading is the sameas selected in the ComboBox1.
Then you have calculated the position of the cell below it (rw & col)
Then in this line you set this cell (below the correct heading to the value in Worksheets("AuditForm").Range("Total").Value
So that is the same value regardless of what the user chooses.
This mean that the source worksheet (in this example Worksheets("AuditForm")) needs to change with the selection of your Combobox1
OK. then about the last line. This sets the currentCell to something else, but then the sub exits, and so this is not used.
Anyway, it is good practice (as VBA does not do this automatically) to set any objects you declared to 'Nothing' before you exit.
You know probably how Windows slows down over the course of a day due to deminishing resources. This is one of the reasons.
So last line:
set currentCell = Nothing
lastly youdon't need the rw an col. You can access the cell below currentCell with currentCell.Offset(1,0)
Your code then becomes:
Code:
Option Explicit
Private Sub ClearButton_Click()
Worksheets("AuditForm").Activate
Range("H11:L15,H17:L21,H23:L27,H29:L33,H35:L39").Select
Selection.ClearContents
ComboBox1.Activate
End Sub
Private Sub UpdateButton_Click()
Dim rw As Integer
Dim col As Integer
Dim currentCell As Range
Set currentCell = Worksheets("5-S Assessment Charts").Range("B13")
While currentCell <> ComboBox1.Text
Set currentCell = currentCell.Offset(0, 1)
Wend
currentCell.Offset(1, 0) = Worksheets("AuditForm").Range("Total").Value
Set currentCell = Nothing
End Sub
