Store an action to remember later

[PFDave]

Hi,

Struggled to think of the best way to title this but hopefully I can explain clearly.

I have a file where users are taken through steps by clicking on shapes on different sheets.

There will be instances where a user can continue with the usual steps, however if they select NO to certain questions (or click a certain numbered shape) I want to be able to remember that for when they reach the final step where I will either display a MsgBox or direct them to another replica slide but with slightly different wording.

Can this be done? Or what are peoples thoughts on this?

Essentially the situation is to try to fix the issue yourself, but if you can't connect the system you need to in order to fix the issue, you carry on trouble shooting with someone on the phone and then once you reach the end you then need to follow some steps to get yourself to connect to the system for next time. (the reason this isn't done at the time, is because it takes 20 minutes to complete and the issue they are trying to fix is a higher priority so must be done first)

Regards

Dave

[EssoExplJoe]

Store their answers to each step in an array of types:

Type
StepNo as integer
Answer as string
End type

[PFDave]

Thanks for the input Esso.

Could you elaborate on this please with an example?

Thanks again

[EssoExplJoe]

Just dimension an array of the types;

Type steprecord

Answer as string
End type

dim steps() as steprecord
redim steps(nosteps)

After each step is completed, set the step answer in the array. You can save the array to a worksheet after each answer if you want under a username, etc..

[PFDave]

Thanks Esso,

But could you provide a working example, I've never used type before or usernames in excel, but this sounds exactly like the kind of thing I would like to use.

Thanks again

[EssoExplJoe]

Define the user type:

Type Questions
  User as string
  RecordNo as long
  SessionDate As Date
  Answers() As String
End Type

When the user restarts a session, get his user name and date of original session then get his answer record:

Function Get_AnswerRecord(User As String, sessiondate As Date) As Questions
'When user logs on, retrieve the answer record from the User's last session
  'assumse answer records are stored in a worksheet with:
  '   column A as the user
  '   column B as the session date
  '   answers in remaining columns
'Returns record of user answers or user = "" if user and date not found in worksheet records
  
  Dim row As Long, col As Long, lastrow As Long, lastcol As Long, record As Long
  Dim sht As Worksheet, ans As Questions
  Set sht = Worksheets("records")
  'get last used row in worksheet
  lastrow = sht.Cells(sht.Rows.count, 1).End(xlUp).row
  row = 1: record = 0
  'Find the row for the User and session date
  Do While row <= lastrow
    If User = sht.Cells(row, 1) And sessiondate = sht.Cells(row, 2) Then
      record = row
      Exit Do
    End If
    row = row + 1
  Loop
  'Return user as "" if not found
  If record = 0 Then Get_AnswerRecord.User = "": Exit Function
  'fill in question structure
  ans.User = User
  ans.RecordNo = record
  ans.sessiondate = sessiondate
  lastcol = sht.Cells(record, Columns.count).End(xlToLeft).Column
  ReDim ans.Answers(lastcol - 2)
  col = 3
  Do While col <= lastcol
    ans.Answers(col - 3) = sht.Range(Cells(record, col))
    col = col + 1
  Loop
  Get_AnswerRecord = ans
End Function

Update the answer record each time the user answers a question;

Sub UpdateAnswers(User As Questions, questionindex As Integer, answer As String)
'Save answers to to a worksheet
  Dim record As Long, i As Long
  Dim sht As Worksheet
  Set sht = Worksheets("records")
  If User.RecordNo <= 0 Then record = 1 Else record = User.RecordNo
  sht.Cells(record, 1) = User.User
  sht.Cells(record, 2) = User.sessiondate
  If questionindex > UBound(User.Answers) Then ReDim Preserve User.Answers(questionindex)
  User.Answers(questionindex) = answer
  i = 1
  Do While i <= UBound(User.Answers)
    sht.Cells(record, i + 3) = User.Answers(i)
    i = i + 1
  Loop
End Sub

This will give you an idea of how to use user defined types.

[PFDave]

Thanks Esso,

give me something to work with for sure.