Solved: if its not broke... recreate it, using userforms

[gringo287]

Hi guys,

This is my first post, so here goes.


Ive created a real time stats tracker for my colleagues to better control their own stats and help increase their own figures and i have to say that it works really well and has made a consideral difference. Im now however quite keen to improve it as it doesnt look as professional as i would like.

what i would like to do is switch it over to useform controls.

The main focus that i mustnt lose sight of is that it must remain as simple as possible for the end user so they dont get scared of it and stop using it.

I dont want the user to have to type anything. It needs to remain as much as possible based on spin buttons.

The other addition that ive been working on with the existing tracker is keeping a running total of the weekly average. as you can see from the attachment, the user currently presses the "day1,2 etc" button to paste the real time stats to the day range that produces the running average. the advisor can then send a copy of the daily stats or the weekly round up to the manager. This however, i find to be limited as it only shows a visual representation of the stats, that although are good to see, they are not functional.

what i want to achieve, i swapping the day 1,2 buttons to buttons that paste the stats to a seperate sheet that can then be used to create team stats/leaderboards (i have a dynamic RAG formula set up that will create this leaderboard).

I obviously dont expect someone to come along and do all this form me, but i would be very grateful if a kind soul could get me started, with the spin button control.

Ive had a tinker with it already, after a good few hours "googling", but i cant seem to get the spin button to add to the previous entry from the last entry.

i.e - advisor takes call/submits input for call in userform/submits form/next call... curently overwrites last call.

Many thanks in advance

[gringo287]

Hi

I seem to have cracked the spin button malarky.

My next task would be add a combo box that the user would select the product that they have offered to the customer that has a value attached to it, so that their selection adds to the accumilated revenue/offers percentage.

Once again, any assistance is appreciated

[Bob Phillips]

Personally, I looked at your workbook, and aside from it being very painful to look at, I could not see what you wanted as the spin buttons on the worksheet seemed to be doing what you were asking for help with.

On the latest request, I cannot see what you are referring to when you say that ... user would select the product that they have offered to the customer that has a value attached to it, so that their selection adds to the accumilated revenue/offers percentage.

It may be just the business and the bad use of colour that is distracting me, but I feel that I have to work too hard to try and understand what you are saying.

[gringo287]

ok... ego dented, ill try and limp on

As i mentioned, im aware that the look of the tracker isnt very professional looking (i didnt feel it necessary to mention, that this is my first attempt at spreadsheets and or vba of any kind).

I havnt set up the list that the combo box would reference from, as at the moment, all the product names and values are on the same sheet as per all the ugly spin buttons in the middle. My aim now, is to remove all the data that the user doesnt need to see and just have one userform that houses the necessary spin buttons plus the one combo box, whereas, each time the user submits the info from the call taken (product offered, from the combo box), it will add to the previous entry and thus updating the users stats.

[Bob Phillips]

It is not the spinbuttons that are ugly, although there are a lot of them and I would question whether a normal person could grasp the whole worksheet, but it is the use of colour. Colour should be used sparingly, to bring attention to important areas of the visual palette, not colour everything. And it is far better to use soft, pastel colours that bold strong colours. Only use bold colours sto shock the user, when they have missed something very important, not as a standard.

But again, I am not sure as to what you are trying to do with products. Giving the high-level as you have is good, but you also then need to give more detail, it is very hard for us to read between the lines. Are you saying that you basically want just one spinbutton, and that the product that it applies to will be selected from a combobox? If this is so, where will you stor the product data to retriev from and to save to when the combobox selection is changed?

[gringo287]

Hi

Hopefully, this should answer your questions. ive not added the code for the spin buttons yet, although i do have it and it works fine. Ive been attempting to adapt some code from the contextures website.

It already, pretty much suits my needs from the combo box perspective. The problem im having, is adding another text box to it. could someone have a scan through it to see where im going wrong. please feel free to point and laugh as im sure its staring me in the face, although as i mentioned previously, i am new to vba. Below is the code and here {{.Cells(1Row, 6).Value = Me.txtadv.Value}}, is where it fails.:help





Option Explicit



Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lPart = Me.cboProd.ListIndex

'check for a part number
If Trim(Me.cboProd.Value) = "" Then
Me.cboProd.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboProd.Value
.Cells(lRow, 2).Value = Me.cboProd.List(lPart, 1)
.Cells(lRow, 3).Value = Me.cboLocation.Value
.Cells(lRow, 4).Value = Me.txtDate.Value
.Cells(lRow, 5).Value = Me.txtQty.Value
.Cells(1Row, 6).Value = Me.txtadv.Value

End With

'clear the data
Me.cboProd.Value = ""
Me.cboLocation.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtadv.Value = 1
Me.cboProd.SetFocus


End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim cProd As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cProd In ws.Range("PartIDList")
With Me.cboProd
.AddItem cProd.Value
.List(.ListCount - 1, 1) = cProd.Offset(0, 1).Value
End With
Next cProd

For Each cLoc In ws.Range("LocationList")
With Me.cboLocation
.AddItem cLoc.Value
End With
Next cLoc





Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtadv.Value = 1
Me.cboProd.SetFocus

End Sub

[Aussiebear]

Where does the
.Cells(lRow, 2).Value = Me.cboProd.List(lPart, 1) get its value from?

[gringo287]

Hi

Im glad you ask, because thats where ive been aiming my attention, while trying to resolve this. The LookupLists sheet in the sov column is where it gets the value from . I think a look at the original code will help point out what im missing.



Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData") 'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
lPart = Me.cboPart.ListIndex 'check for a part number
If Trim(Me.cboPart.Value) = "" Then
Me.cboPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If 'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboPart.Value
.Cells(lRow, 2).Value = Me.cboPart.List(lPart, 1)
.Cells(lRow, 3).Value = Me.cboLocation.Value
.Cells(lRow, 4).Value = Me.txtDate.Value
.Cells(lRow, 5).Value = Me.txtQty.Value
End With 'clear the data
Me.cboPart.Value = ""
Me.cboLocation.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboPart.SetFocus
End Sub

[gringo287]

thats really annoying, dont know why that happened. It wont even let me edit it. sorry guys

[Aussiebear]

This line .Cells(lRow, 2).Value = Me.cboPart.List(lPart, 1) will be trying to look for a value from the form

[gringo287]

**my last reply wont make any sense. when i posted the code last night on my laptop, it shown the code all on one line.., on my pc again and i can see that its fine. strange**

but when i remove my line of code

.Cells(1Row, 6).Value = Me.txtadv.Value

it works fine, even with the other extra bits that ive added?. as far as i can tell from http://www.contextures.com/xluserform02.html#SetUp , the line your refering to is there so that the part description (sov in my case), can be recored with the corresponding part id (product) on the PartsData sheet.

[gringo287]

Ok, i think i this is where my newbness will become glaring clear..
this is the code for the actual form. :doh:


Private Sub UserForm_Initialize()
Dim cProd As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cProd In ws.Range("PartIDList")
With Me.cboProd
.AddItem cProd.Value
.List(.ListCount - 1, 1) = cProd.Offset(0, 1).Value
End With
Next cProd

For Each cLoc In ws.Range("LocationList")
With Me.cboLocation
.AddItem cLoc.Value
End With
Next cLoc





Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtadv.Value = 1
Me.cboProd.SetFocus

End Sub

[gringo287]

Well, i wont forget this experience in a hurry. so it seems that the letter l and the NUMBER 1 are pretty much identical in vba. As im basically learning by doing, im not actually sure on the reason for the Lrow. 1row, sort of made sense at the time. Any chance someone still has enough patience for to explain this to me.

.Cells(lRow, 6).Value = Me.txtAdv.Value
.Cells(1Row, 6).value = Me.txtAdv.Value

[Bob Phillips]

You should always Option Explicit at the head of your code modules, then you will be forced to declare your variable names, and then any typos will be caught by the compiler.

[gringo287]

I think ill get that tattood the the back of my hand. cant believe how long i stared at that line before i spotted that.