Issue with a VBA form on Excel...Pls help

[ryanadam]

Hi gents,
i am new to this forum, I am not savvy in Exce,I use the basic
Recently my boss asked me to prepare a sales activity tracker
I did it is working perfect when i use it. nowhewants all the team to use it
someof them are not familiar with Excel,so I am trying to create a a formwhere they will input their data and do not have to touch the file itself ( I locked all the formulas as well)

My data is organized the following way
and starts at row 27 (data starts going in 27)
<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=450 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff; WIDTH: 84pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18 width=112>Month</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #99ccff; WIDTH: 35pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=47>Week</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #99ccff; WIDTH: 57pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=76>Day#</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #99ccff; WIDTH: 51pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=68>Date</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #99ccff; WIDTH: 52pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=69>Refer #</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #99ccff; WIDTH: 59pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=78 x:str="Product ">Product </TD></TR></TBODY></TABLE>
wrote this VBA script:

Private Sub CancelButton_Click()
Unload UserForm1
End Sub
Private Sub EducationLevel_Click()
End Sub
Private Sub Label1_Click()
End Sub
Private Sub OKButton_Click()
Sheets("MASTER_SHEET").Activate
NextRow = Application.WorksheetFunction.CountA(Range("B:B")) + 17
NextRow = Application.WorksheetFunction.CountA(Range("B:B")) + 17
Cells(NextRow, 5) = TextName.Text
If Optiongold Then Cells(NextRow, 6) = "GOLD"
If Optionplatinium Then Cells(NextRow, 6) = "platinum"
If Optionsilver Then Cells(NextRow, 6) = "silver"
If OptionACTIV Then Cells(NextRow, 6) = "ACTIV"
If OptionINS100 Then Cells(NextRow, 6) = "INS100"
If OptionINS200 Then Cells(NextRow, 6) = "INS200"
If OptionINS300 Then Cells(NextRow, 6) = "INS300"
If OptionINS400 Then Cells(NextRow, 6) = "INS400"
If OptionINS500 Then Cells(NextRow, 6) = "INS500"
If OptionINS600 Then Cells(NextRow, 6) = "INS600"
If OptionINS700 Then Cells(NextRow, 6) = "INS700"
If OptionINS800 Then Cells(NextRow, 6) = "INS800"

TextName.Text = ""
TextName.SetFocus

End Sub
--------------------------------
Now what happens:
1-The first input goes into the row 27 fine but after a couple of data entered it keeps on replacing of the last cell

I tried someother formulas but stillit does not seem tohelp
Please I need this in my new Job/Position,if anyone can help i wouldhighly appreciate

 

[Norie]

Make sure you reference the correct worksheet throughout the code.

Perhaps something like this, which is untested.

Private Sub OKButton_Click()
    With Sheets("MASTER_SHEET")
        NextRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
        .Cells(NextRow, 5) = TextName.Text
        If Optiongold Then .Cells(NextRow, 6) = "GOLD"
        If Optionplatinium Then .Cells(NextRow, 6) = "platinum"
        If Optionsilver Then .Cells(NextRow, 6) = "silver"
        If OptionACTIV Then .Cells(NextRow, 6) = "ACTIV"
        If OptionINS100 Then .Cells(NextRow, 6) = "INS100"
        If OptionINS200 Then .Cells(NextRow, 6) = "INS200"
        If OptionINS300 Then .Cells(NextRow, 6) = "INS300"
        If OptionINS400 Then .Cells(NextRow, 6) = "INS400"
        If OptionINS500 Then .Cells(NextRow, 6) = "INS500"
        If OptionINS600 Then .Cells(NextRow, 6) = "INS600"
        If OptionINS700 Then .Cells(NextRow, 6) = "INS700"
        If OptionINS800 Then .Cells(NextRow, 6) = "INS800"
    End With
    TextName.Text = ""
    TextName.SetFocus
End Sub

 

[ryanadam]

Thanks NORIE
I tried it it seems its working, Thank you again you are an angel...
I will try to finalize it and see if the formula is stable when i enter massive data, Thanks a lot.

 

[Norie]

What formula are you referring to?

There's no formula, unless you mean the CountA, in what you posted and there's definitely no formula in the code I posted.

If you are referring to a formula on the worksheet I think the only way we could help tell if it would be stable would be to see the formula.

Also it would depend on what you actually mean by 'stable'.

 

[ryanadam]

Thanks NORIE,see I am so VBAignorant that I have mistaken what you sent with a formula.
It is working perfect the only thing is
1- if the first text box is empty it erases the privious record.
2- Can I add as many products as I need
Looking forward to hearing from you. and thanks for the "Formula"
Have a lovely day!

 

[Norie]

Perhaps you're getting a little confused again - what do you mean by the first textbox?

There only appears to be one.

If you mean that on the worksheet there is a blank where the name should be then that would cause problems when trying to find the next row.

As for multiple entries, it really depends what you mean.

As is, as far as I can see, you can enter as many as you want - obviously limited by the no of rows available.

If you wanted to do some sort of batch thing then you would probably need to add something like a listbox.

Then when you hit the OK button then the entries are added to it, not the worksheet.

Then once you've made all the entries you want, checked them over etc you could hit a submit button which would put all the entries on the worksheet in one go.

Doing something like this could be as straightforward or as complicated as you want it to be.

For example you could add functionality to not just add 'records' but to edit them, delete them, even duplicate them.

All of that is possible but you would have to do it yourself.

 

[ryanadam]

Thanks Noriefor all your help, i got it working,now i need to start studying VB but thay was a good intro for two days i am getting familiar with VB.
hope to talk to u soon

 

[MeghaJ]

Hello Norie,
Actually i m also facing some problem while finding last row..

I m using following code

NextRowValue = Range("A" & Rows.Count).End(xlUp).Row

Actually the heading is merge of some rows A10 to A12.. so it is not returning me value as 13 it is returning 10.
How i should use it now..? i cannot write

NextRowValue = Range("A" & Rows.Count).End(xlUp).Row + 3

because this value i need to track... Could u please help me?

 

[Norie]

Why can't you add 3?

Does doing so give you the correct answer?

By the way this illustrates why it's a bad idea to use merged cells, especially when using code.

 

[MeghaJ]

Actually i m genrating a report which needs to take data from different file and i need to have this NETROWVALUE in many modules.. hence i can not add 3.. and this report i m doing for my Boss tht i can't change also.. is there anyother way?