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

ryanadam

New Member
Joined
Aug 11, 2009
Messages
4
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Make sure you reference the correct worksheet throughout the code.

Perhaps something like this, which is untested.
Code:
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

New Member
Joined
Aug 11, 2009
Messages
4
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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

New Member
Joined
Aug 11, 2009
Messages
4
Thanks NORIE,see I am so VBAignorant that I have mistaken what you sent with a formula:LOL:.
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":LOL:
Have a lovely day!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Perhaps you're getting a little confused again - what do you mean by the first textbox?

There only appears to be one.:eek:

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

New Member
Joined
Aug 11, 2009
Messages
4
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

Board Regular
Joined
Jul 2, 2009
Messages
102
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jul 2, 2009
Messages
102
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?
 

Forum statistics

Threads
1,082,253
Messages
5,364,045
Members
400,776
Latest member
JimmyLee

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top