vba macro code

[JLSHARP92]

I have a form setup which will be used to add students to my system. The students names are entered in one field, their group number in another field and then their expected results in the next field. I have come up with a macro code to do this but unfortunately, there is an error in my code and was wondering if somebody could help me with this. Heres my code:

Sub Macro1()

'stops the screen from flickering
Application.ScreenUpdating = False

'declares variables
Dim RowNum As Integer
Dim Row As Integer
Dim NumStudents As Integer

Dim Studentname As String
Dim Groupnumber As String
Dim Studentmeg As String

Dim cellref As String
Dim cellref2 As String
Dim cellref3 As String

'sets the row number to 1
RowNum = 1

Studentname = Range("Studentname")
Groupnumber = Range("Groupnumber")
Studentmeg = Range("Studentmeg")

'move to user details worksheet
Sheets("NewStudentDetails").Select

For Row = 1 To NumStudents

'sets cell references to rows
cellref = "A" & RowNum
cellref2 = "B" & RowNum
cellref3 = "C" & RowNum


If Range(cellref) = Studentname Then

MsgBox "You must enter a username and password in order to add a new user to the system"
Sheets("AddNewStudent").Select
Exit Sub

ElseIf (cellref2) = Groupnumber Then

MsgBox "You must enter a username and password in order to add a new user to the system"
Sheets("AddNewStudent").Select
Exit Sub

If Range(cellref3) = Studentmeg Then

MsgBox "You must enter a username and password in order to add a new user to the system"
Sheets("AddNewStudent").Select
Exit Sub

End If

If ActiveSheet.Cells(Row, 1) = "" Then

Range(cellref) = Studentname
Range(cellref2) = Groupnumber
Range(cellref3) = Studentmeg

MsgBox "The new student has been added to the system"

Else

RowNum = RowNum + 1

End If

Next Row

End Sub

[Simon Lloyd]

What is the fault?, we're hardly going to build a workbook to test your code to find the fault, tell us what the fault number is and on which line it falls down.

I've shortened your code a little for you:Sub Macro1()

'stops the screen from flickering
Application.ScreenUpdating = False

'declares variables
Dim RowNum As Integer, Row As Integer, NumStudents As Integer
Dim Studentname As String, Groupnumber As String, Studentmeg As String
Dim cellref As String, cellref2 As String, cellref3 As String

'sets the row number to 1
RowNum = 1

Studentname = Range("Studentname")
Groupnumber = Range("Groupnumber")
Studentmeg = Range("Studentmeg")

'move to user details worksheet
Sheets("NewStudentDetails").Select

For Row = 1 To NumStudents

'sets cell references to rows
cellref = "A" & RowNum
cellref2 = "B" & RowNum
cellref3 = "C" & RowNum


If Range(cellref) = Studentname Or (cellref2) = Groupnumber Or Range(cellref3) = Studentmeg Then
MsgBox "You must enter a username and password in order to add a new user to the system"
Sheets("AddNewStudent").Select
Exit Sub
End If

If ActiveSheet.Cells(Row, 1) = "" Then

Range(cellref) = Studentname
Range(cellref2) = Groupnumber
Range(cellref3) = Studentmeg

MsgBox "The new student has been added to the system"

Else

RowNum = RowNum + 1

End If

Next Row

End Sub

[ALe]

Simon is right.
Attach the file so it's easier to help you.

[JLSHARP92]

Ok, I'll start again. Ive made more ammends to my form that I have created on excel and have came up with a different request. I have a form which adds a students name, group number and student grade predictions. The fields are named as follows:

Student name = StudentName
Group number = GroupNumber
Student predicted grades = StudentMEG

What I need to do is to be able to type in a students name, group number etc. and press a button which transfers that data onto another worksheet which already has existing students and is called Unit 1. The students name must be in collumn A27 going downwards, the group number must be in collumn B27 going downwards and the student grade predictions must be in collumn AD15 also going downwards. I also need a message box upon clicking the button to let the user know that the data has been transferred and the new user has been added to the system. I hope this helps and I hope that the code I provided is sufficient enough to resolve my problem.

P.S. I get an error which is the Next Row part of the code which turns yellow.

[JLSHARP92]

.. and I am sorry I spelt column wrong lol :fright:

[GTO]

Greetings JL,

Could you please attach a workbook with your present code and userform. You can fake some student data to substitute any sensitive data.

[JLSHARP92]

Yeah sure, No need to change any student data its all made up anyway! Im new to this site, how do you attach files? :dunno

[JLSHARP92]

I have attached my workbook.

[GTO]

Okay, below the 'Quick Reply' box is the <Go Advanced> button. Press this, and when the new window loads, you can scroll down and see Manage Attachments.

Edit: I don't see it yet, try again :-)

[Kenneth Hobs]

You need to explain what you need help with. Explain what to do to get the "error" and what happens when it works right.

[JLSHARP92]

Well an error message must show up if the group number does not equal 1 or 2, the MEG must equal either A,B,C,D,E or U and a confirmation message must show up saying the new student has been added if no errors occur. There should be no reason to add validation to the students names.

[JLSHARP92]

any ideas anyone? : pray2:

[Kenneth Hobs]

You need to go into more detail to get the help that you want. Take your example workbook and explain what to input and what should do when something is done.

The more simple, the easier it is to help.

[JLSHARP92]

You need to go into more detail to get the help that you want. Take your example workbook and explain what to input and what should do when something is done.

The more simple, the easier it is to help.

I'm pretty sure I've explained what I need to happen in the workbook in this thread :115:

[Rob342]

JL

Had a sneak at your workBook "Phew" there are quite a few errors in the various modules.

The buttons you are using are not calling the correct procedures

If you detail what you want i may be able to sort some of out, but thats going to take some time.

As everybody says you need to explain it step by step exactly what you are expecting & trying to achieve.

There are some excellent people in this forumn with a far greater knowledge than me who are willing to help and give up some of there valuable time but they also need you to be involved.

Rob

[JLSHARP92]

Here is a updated version of my database. I believe it has greatly improved since the last upload of the database. If anybody would care to take a look at it and let me know if Im going wrong anywhere.

[Rob342]

JL

Your database will not work the way you have it, it needs to be in a defined order to work correctly, the tab sheets have spaces in the name and the vlookups are not correct.

You still haven't explained clearly, start from the time you login on the sheet and what data goes where and what the other sheets do ?

Write all the process's from the start ie 1 to 50 if necc, if you dont want to add it here then include it on a e mail.

Rob

[JLSHARP92]

Login form - the login form is completed and doesnt need any ammendments.

Main menu - the main menu is the same, no changes need to be made.

Add late student - This form needs a little work. What needs to happen is, the user will select a group number and the student meg from the drop down boxes and then they must enter a students name into the text field. After this has been done, the add late student button will be pressed and the data which has been entered on the form must be transferred onto the unit 1 worksheet with the rest of the students. You will notice that the students are categorised into their groups and once the group has been chosen on the form, the students names will need to be transferred into that group.

Grade boundaries - this form doesnt need anything doing to it at the moment.

Enter strand marks - This is the main form I need sorting out fast. Very similar to the add late student form but the data is a little different. The group number, student name, unit, and the strand marks are all chosen using a drop down box. The student name must be chosen first, then the group of that student then the unit in which the student is receving their strand marks for, the strand and then finally the mark they received. The marks will be copied to the unit 1,2 or overall worksheet (depending on which unit the user has chosen) and the strands are actually on the worksheets. The strands are labelled A,B,C,D,E and F.

I hope this helps.

[Rob342]

JL

Can You post a copy of the text file. change any user names if req.


Rob

[JLSHARP92]

The text file for what?

[JLSHARP92]

Here is the only text file I have used on the system..

[Rob342]

this file

"U:\IT\Final Unit 8 & 12\Unit12StudentData.txt" For Input As #1

The login in forms & the rest of the "Forms" are not actual forms and the buttons are not Active X control buttons, you will have problems assigning automatically any of the buttons to vba code.

1 You can keep the layout as it is & insert active x controls to handle the code.

2 Use a correct "USERFORM" and controls to handle the code.

Do you want me to assign the active x controls & put the code behind them ?

3 who has access to this is it everbody or just teachers ?

4 How are you currently operating the vba code ?


Rob

[JLSHARP92]

I can't use USERFORMS as I have been told to use the fields in excel instead. I created this system by myself and I am the only person to handle the excel document.

[Rob342]

JL

Have made a start on some amendments to login form to start and changed the code so it works.
Can you look at the test copy & let me know if this is what you want ?

[JLSHARP92]

Thank you very much for this, I appreciate this massively. The main one i really need to work is the strand marks form. Thank you very much for this.

[JLSHARP92]

P.S The strand marks form needs the 'paste special' function.

[Rob342]

JL

Have completed as much as i can.

You need to look at the Grade Boundaries sheet & redesign it otherwise it wont work.
I have inserted the 1st part of the code for that module & left a message box for you to complete the code when you have redesigned the worksheet into a logical order.

My thanks to BrianBH for getting me out of the Pickle.

Have Fun

Rob

[BrianMH]

No problems. I actually enjoy coding. Been surfing the forum this weekend looking for things to do. Nerdy or what?

[Simon Lloyd]

Is this for an assignment? or for use within the college?

[JLSHARP92]

This is for an assignment. Thank you very much Rob you have saved me a lot of time and effort. I will get in touch with you tomorrow, there are a few things I dont understand.

[Simon Lloyd]

Firstly JLSharp, we do have rules on help with assignments/homework, please read them (most forums also have this policy).

Secondly there are some things that you'd need to change to make this more effective: for the password box use a textbox then you can view it's properties and change PasswordChar for a mask such as * so that the password is never on view in the formula bar. You should also hide the sheets using xlVeryHidden this way the sheets will not be present in the menubar Format>Sheet>Unhide as they can only be made visible by code. Rather than setting the range for the last cell and looking up you should use the rows.count convention (Range("A" & Rows.Count).End(xlUp).Row) this way it will be compatible with other versions of excel, further more rather than then adding another variable to find the last row plus one you should use .Offset(1,0) (Offset(Row to offset, Column to offset)). You try to activate sheets prior to making them visible this will cause you all sorts of problems.

You don't need to (but for your assignment you might) call a procedure by first stating the module i.e Call Module1.MyMacro can just be Call MyMacro, you should also name your modules as something useful rather than just module. lastly you force the application to quit rather than Thisworkbook.Close.

Just some things you should look at :)

[Rob342]

JL/Simon

Comments noted, did not relalise this was an assignment until 1/2 way through.

I have reversed the visible & activate bits of code for you.

Please note As Simon says, you can use the other method to find the last cell on a sheet both work, if you are appending data to the sheets on every occasion then you will need that formula that Simon suggests.

For this spreadsheet you will never get to that range (A65536)

The "This Workbook.close" although it still good pratice to use later versions of Excel from 2000 on, according to Microsoft "Application.Quit" has a routine built in that has a Auto_Close function, so do you really need "Thisworkbook.close".

Simon
Can you provide JL with an example of the routine for looking past 65536 range ? for future reference.


Rob

[Simon Lloyd]

.......... Rather than setting the range for the last cell and looking up you should use the rows.count convention (Range("A" & Rows.Count).End(xlUp).Row) this way it will be compatible with other versions of excel,......Rob, i did :)