VBA Excel Coding extension and modification

Question

I have made an assessment system for a project using Microsoft Excel and I wanted to to make it so that you could use the same drop down menus twice.

Enter the data and then for the spreadsheet to retain that data and allow you to overwrite it but still maintain the data but to be dependant on the value of a data validation drop down list.

I have been given the code for this and it works however only for a section of the spreadsheet.

I wish to have the same effect however use a different drop down menu and for it to affect a different section of the spreadsheet.

Please feel free to ask for the actual spreadsheet or code.

Here is the Code:

Option Explicit

Public Sub Worksheet_Change(ByVal Target As Range)

   ' This Sub is a standard VBA event handler. It is automatically invoked
   ' every time the content of any cell in this worksheet changes

   ' We are only interested if the user picks a different type of
   ' grade. A named range GradeType was created to name this cell.
   ' This allows the worksheet format to change without having to change
   ' this code.
   If Target.Address = Sheet1.[GradeType].Address Then

      ' So the user doesn't see each invidual worksheet change as it happens
      Application.ScreenUpdating = False

      ' Where the current data will be saved to
      ' These are in the first row, so the number of columns has
      ' to be determined on the fly based on how much data is there
      Dim FirstSaveTo As Range
      Dim LastSaveTo As Range

      ' Where the previous saved data will be restored from
      Dim LastRestoreFrom As Range
      Dim FirstRestoreFrom As Range

      ' Use variables to define the relevant spaces in the Save sheet
      ' depending on what grade type the user selected
      If [GradeType] = "Attainment" Then

         Set FirstSaveTo = Save.[AttainmentStart]
         Set LastSaveTo = Save.[AttainmentEnd]

         Set FirstRestoreFrom = Save.[EffortStart]
         Set LastRestoreFrom = Save.[EffortEnd]

      Else

        Set FirstRestoreFrom = Save.[AttainmentStart]
        Set LastRestoreFrom = Save.[AttainmentEnd]

        Set FirstSaveTo = Save.[EffortStart]
        Set LastSaveTo = Save.[EffortEnd]

      End If

      ' Save current data

      ' Clear previously saved data
      Save.Range(FirstSaveTo, LastSaveTo).EntireColumn.ClearContents
      ' Copy current data
      Sheet1.Range(Sheet1.[AssessmentFirst], Cells(Sheet1.UsedRange.Rows.Count, Sheet1.[AssessmentLast].Column)).Copy
      ' Paste
      FirstSaveTo.PasteSpecial xlPasteValues

      ' Restore saved data

      ' Clear current data
      Sheet1.Range(Sheet1.[AssessmentFirst], Cells(Sheet1.UsedRange.Rows.Count, Sheet1.[AssessmentLast].Column)).ClearContents
      ' Copy saved data
      Save.Range(FirstRestoreFrom, Save.Cells(Save.UsedRange.Rows.Count, LastRestoreFrom.Column)).Copy
      ' Paste saved data
      Sheet1.[AssessmentFirst].PasteSpecial xlValues

      ' Deselect copy area
      Application.CutCopyMode = False

      ' Put user back where he started
      [GradeType].Select

      Application.ScreenUpdating = True

   End If

End Sub

Yes, please provide the code. You can edit your question and paste it at the bottom. — PowerUser
– PowerUser, Commented Dec 13, 2011 at 22:40
@PowerUser, here is the code for the sheet, the code works however I wish to modify it, is it possible for me to attach my spreadsheet? or possibly email it to you directly? I need it would be a lot easier to understand. — Vishnu Surjid
– Vishnu Surjid, Commented Dec 13, 2011 at 22:52
@VishnuSurjid: upload it in google docs or similar services and give the link to it. — Kannan Suresh
– Kannan Suresh, Commented Dec 14, 2011 at 1:25

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

0

Your code is getting currently applied to the Named Range GradeType.

If you want to apply your code to another drop-down list, you can change this line:

If Target.Address = Sheet1.[GradeType].Address Then

And adapt it to whatever you need (don't forget to create a new named range first).

In order to do this, have a look at:

what is a named range and how to define it
how you can learn some vba: Programming Excel and VBA: Basic Syntax and Examples Tutorial

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Dec 14, 2011 at 8:47

JMax

26.7k12 gold badges74 silver badges89 bronze badges

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

1 Comment

Vishnu Surjid Over a year ago

,I have attempted this but I need to apply it to another list but also to another area of the spreadsheet

Collectives™ on Stack Overflow

VBA Excel Coding extension and modification

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related