CODEWITHSUNDEEP
vbaexcel
Nickvi1
Nickvi1

Reputation: 3

why does my VBA code that works in module not work as expected when assigned to a worksheet and a button

I have a workbook that is essentially an automated test, marking and feedback tool for end of topic tests for students. On the '701Test' sheetThey input their teaching group via a drop down list and the select their from subsequent list. They answer the multiple choice questions and press a button when finished. The button takes them to a 'results' page which gives their marks for each question, give feedback for incorrect answers and gives a total score. They then hit the finish button which generates a PDF copy of the mark sheet in their my documents folder and then emails a copy to themselves and the Schools email account. At this point I also wanted to post the final score to the students record on a central registry using a loop through the student list to find the name and offset to post the Score value from the 'Results' page and finally return to the test page. This last bit I wrote the code for in a module and it executes perfectly, but when added to the main code and run from the button the loop part fails to execute but the return to the test page does work, but no error is recorded for the loop failure.

Here is the 'Results' page code in full the 'With Central reg' bit at the bottom is the problem, any help is greatly appreciated.

Private Sub CommandButton1_Click()

  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
  Dim cell As Range
  Dim Students As Range

  Title = Range("D1").Value
  sname = Range("B2").Value
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\" & sname & Title & ".pdf"


  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With

  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0

  With OutlApp.CreateItem(0)

    .Subject = Title
    .to = Range("B2").Value  ' <-- Put email of the recipient here"
    .CC = "" ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "Yr 7 701 EOT test attached in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & "KDS ICT Dept" & vbLf & vbLf
    .Attachments.Add PdfFile

    Application.Visible = True
    .Display
  End With

  If IsCreated Then OutlApp.Quit

  Set OutlApp = Nothing


  With CentralReg


    For Each cell In Range("A2:A250")
    If cell = Range("Results!B2").Value Then
    cell.Offset(0, 4).Activate
    ActiveCell.Value = Range("Results!B27").Value
    End If
    Next


End With

End Sub

Upvotes: 0

Views: 295

Answers (2)

urdearboy
urdearboy

Reputation: 14590

I believe you are trying to refer to CentralReg which is a worksheet, which means you should qualify it as such.

Also, you should not dim variables that are similar to defined objects/properties in VBE. Try MyCell instead of cell (good practice, not required).

I am assuming you want to see if the value on sheet CentralReg in Column A is equal to sheet Result B2. If this condition is met, your MyCell will take on the value equal sheet Result B27

Dim MyCell As Range
Dim Result, NewValue as Variant
Result = ThisWorkbook.Sheets("Result").Range("B2")
NewValue = ThisWorkbook.Sheets("Result").Range("B27")

With ThisWorkbook.Sheets("CentralReg")
    For Each MyCell In .Range("A2:A250")
        If MyCell = Result Then MyCell.Offset(, 4) = NewValue
    Next MyCell
End With

Upvotes: 1

Dan Donoghue
Dan Donoghue

Reputation: 6216

That with statement is useless as nothing actually uses it within the construct.

Delete with CentralReg and End with and it will work.

alternatively if CentralReg IS something like a sheet then you need to precede your code with a . so this: Range("A2:A250") becomes this: .Range("A2:A250") and so on, the . tells the code that it is related to whatever your with construct surrounds

Upvotes: 0

Related Questions