Posts by dodger7

Re: Pivot Table Query

sorry should have said.

Im trying to gather the following:

amount of requests coming from customers
what demand/exact demand they are looking for
are they registered and was the answer available online
the source of the email
what draft email did we send the customer

the date/policynumber/scheme number/additional comments/submitted by/dept are all irrelevant.

Hope this explains things further. Im not sure if this is do-able in the one table

Many thanks for your help!

Hi Folks

attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big)

Ive been trying for about 3 days now to make a pivot table to summarise this data but keep getting crazy results.

Would someone be kind enough to help me out?

thanks

Re: Named Range Not Picked Up In Userform

I eventually got this to work, so thought id post the answer incase someone else has this problem and does a search.

As the problem appears to be with the indirect, I did the following:

Redefined "listsource" as "=EMPData!$IV$1"

then used

Me.Controls("listsol").RowSource = Range(Range("listsource").value).address

nesting the ranges gave me the address of the named range in the cell that listsource refers to.

Jamie

Re: Named Range Not Picked Up In Userform

can I give this a bump in case some of the experts missed it, and might be able to help

Re: Named Range Not Picked Up In Userform

is there a way of putting the actual range of the dynamic range into another cell that will automatically change with the dynamic range? IE if your dynamic range is A1:A19 have cell AA1 show simply A1:A19, but if another line is added then cell AA1 will automatically show A1:A20 in line with the dynamic range?

Re: Named Range Not Picked Up In Userform

I dont understand though, because the row source is dynamic named range. so surely the cboboxes should be OK.

Re: Named Range Not Picked Up In Userform

there is actually a slight difference - this new one has dynamic named ranges. Does userfroms not recognise these?

Re: Named Range Not Picked Up In Userform

thanks for the alternative Bill

its annoying though because Ive done it like this before, the exact same way. In fact you helped me do it. See the second last post in this thread, which you kindly assisted me on:

http://www.ozgrid.com/forum/showthread.php?t=61778

Re: Named Range Not Picked Up In Userform

thanks for your reply.
I put =EMPProblem into the rowsource for cbonature. This worked fine. However, When I click on one of the options from the dropdown, I get an error message as if its not picking up the next named range. Did you get this message?

hi folks

I have a speradsheet for my work, which gives the user problems and solutions to our website. however I cant get the userform to recognise the named ranges I have added. (ie the first combobox wont recognise the rowsource, which is a named range).

I have attached a simplified version for reference.

thanks for your time

Jamie

Re: Duplicate Entries Into A Shared Spreadsheet

thanks Roy, useful page that. Does the workbook have to be open, or will it say run at 8pm every night, even if the pc isnt turned on?

Re: Duplicate Entries Into A Shared Spreadsheet

the reason for different sheets is that the data is broken down by country, postcode, building number, floor, area, department, team, user etc etc and theres far too many different headers to make it readable. I would use a pivot table, but I need to change stuff, add comments etc and it didnt quite work.

however ill look further into a pivot table if this is the only way, ill also have a look at "ontime method"

thanks for your input roy

Re: Duplicate Entries Into A Shared Spreadsheet

ive just heard of a suggestion to write vba so that all the input from sheet 1 to 6 is copied over to sheet 7 at a certain time of night. This sounds quite ridiculous to me. Is this possible? surely theres an easier way...

hi folks

wonder if you can help?

Ive got a spreadsheet with 6 buttons. each button has a different form. when the form is submitted, it records data to a spreadsheet. (ie button 1 records data to sheet 1, button 2 records data to sheet 2 etc). however each button also adds a line to sheet 7, which should be the total. but, as the spreadsheet is shared, sometimes a user will open the sheet, enter data, and another user will do the same. but since user 2 has just submitted their data a few seconds after user 1 and its not yet been saved, both users have entered data into the same line on sheet 7(the code looks up first blank cell in A and adds it to that row).

Is there a way round this?

morning all

I have created a spreadsheet thismorning where the user simply fills in a form and clicks submit, and the data is stored in another sheet.

However What I was wanting to do, is add another sheet and copy the data into that aswell - is it possible to add in a second loop, and have the data from the form copied to the next blank row of the new sheet aswell?

My code is:

Private Sub submitbutton_Click()


ActiveWorkbook.Sheets("Individual Pensions Total").Activate
Range("A5").Select


Do


If IsEmpty(ActiveCell) = False Then


    ActiveCell.Offset(1, 0).Select
    
    End If
    
    Loop Until IsEmpty(ActiveCell) = True
    
    ActiveCell.Value = txtpol.Value
    
    If cbocustomer.Value = "Other" Then
    ActiveCell.Offset(0, 1) = txtcustomer.Value
    Else
    ActiveCell.Offset(0, 1) = cbocustomer.Value
    End If
    
    If cboDemand.Value = "Other" Then
    ActiveCell.Offset(0, 2) = txtdemand.Value
    Else
    ActiveCell.Offset(0, 2) = cboDemand.Value
    End If
    
    If cboExactDemand = "Other" Then
    ActiveCell.Offset(0, 3) = txtexactdemand.Value
    Else
    ActiveCell.Offset(0, 3) = cboExactDemand.Value
    End If
    
    
If opb1 = True Then
    ActiveCell.Offset(0, 4).Value = "Yes"
    Else
    ActiveCell.Offset(0, 4).Value = "No"
    End If
    
If opb3 = True Then
    ActiveCell.Offset(0, 5).Value = "Yes"
    Else
    ActiveCell.Offset(0, 5).Value = "No"
    End If
    
    If cboCRB.Value = "Other" Then
    ActiveCell.Offset(0, 6) = txtCRB.Value
    Else
    ActiveCell.Offset(0, 6) = cboCRB.Value
    End If
    
    If cboadcomments.Value = "Other" Then
    ActiveCell.Offset(0, 7) = txtadcomments.Value
    Else
    ActiveCell.Offset(0, 7) = cboadcomments.Value
    End If


Range("A5").Select


End Sub

Re: Public Variable Not Picked Up In Form Module

thank you guys, as always every post was of help.

much appreciated

hi folks

I have the following code and [v]*[/v] in ThisWorkbook:

Public sLast as Object


Sub Workbook_SheetActivate(ByVal Sh As Object)


    If Sh.CodeName <> "Sheet8" Then
       
       Set sLast = Sh
       
    End If
    End Sub

and my form has the following code:

Private Sub ComboBox1_Click()
    Sheet8.Activate
    Sheet8.Range("IV1").Value = "Problem" & ComboBox1.ListIndex + 1
    Me.Controls("ListBox1").RowSource = "try"
    Me.TextBox1.Value = ""
End Sub


Private Sub ListBox1_Click()
    Me.TextBox1.Value = Cells(Me.ListBox1.ListIndex + 2, Me.ComboBox1.ListIndex + 16).Value
End Sub


Private Sub Exitbutton()


Unload Me


End Sub


Private Sub UserForm_Terminate()


sLast.Select


End Sub

what i was hoping would happen, is when the user exits the Form they are directed back to the previous sheet they were on. I get a runtime error. Can anyone point me out where im going wrong, or better still, advise how I can run my form without actually selecting the sheet (sheet8) where the data for the dropdown box etc resides?

p.s "try" is a [nr]*[/nr]

Many Thanks
Jamie

Re: Show List Depending On Drop Down Selection

Thats exactly what I was looking for Bill,

Thanks very much your your time

Jamie

Re: Show List Depending On Drop Down Selection

Hi Guys

I have another question sorry. Its related to the above so thought I would keep it in the same thread.

I have got an updated spreadsheet attached, thanks to bill and dave for their input. currently when the user click on the make payments button on the first page, the form opens up. once the user choses a problem from the first drop down list, the listbox list is complied accordingly.

What I now need is when the user highlights (or single clicks) the solution in the list box, a more detailed explination is shown in the new box on the right of the form.

Is this possible?

I will be storing all the detailed solutions on the MPData sheet, where the raw data is currently stored. However im unsure the best way to lay it out.

Many thanks again
Jamie

Re: Show List Depending On Selection From Drop Down Menu

Thanks again Bill/Dave, will have a thorough read through that now.

Much appreciated