Posts by scottyP

Re: View A Word Document On A Userform

Yeah, I suppose so, but then you are adding a fair delay to the whole process. : D

I will certainly continue to use this workaround if I can't actually display the word doc itself, but it would be nice if I could just add a component to the form, and tell it to load the required file (wishful thinking maybe).

Thanks

Scott : D

Re: View A Word Document On A Userform

Hi,

Thanks for the reply. Yeah, that's what I'm doing at the moment, however we could be talking about 5000-7000+ forms per year once it's rolled out, and storing them in both formats could take up a lot of space!!!

Scott : D

Hi,

Sorry if this sounds completely weird, but I've been banging my head against the wall for a wee while now.

What I basically want to do is display a Word document within a UserForm.

The UserForm happens to be in Excel.

To complicate matters, the Word Document is protected (my excel App knows the password, it exported the file to begin with).

I know you can select a "Microsoft Office Spreadsheet 11.0" from the toolbox, however I can't see a similar control for a Word Document.

Is this even possible? Any help greatly appreciated! : D

Scott

Re: Linking Cells

Hi,

I'm still not 100% sure what you are trying to acheive, (an example file might be handy in future ), however I think I know what is wrong. The first sub genrated the following formulae;

=SUM(data!C5:C82)
=SUM(data!K5:K82)
=SUM(data!S5:S82)
=SUM(data!AA5:AA82)...

Whereas the second generated;

=SUM(data!D5:D82)
=SUM(data!K5:K82)
=SUM(data!S5:S82)
=SUM(data!AA5:AA82)...

instead of;

=SUM(data!D5:D82)
=SUM(data!L5:L82)
=SUM(data!T5:T82)
=SUM(data!AB5:AB82)...

If this is correct, then you need to update the for loop to;

For tmp = 8 To 211 Step 7

Hope this helps.

Scott : D

Re: Linking Cells

In your first example, you have;

ActiveCell.FormulaR1C1 = "=SUM('data'![B]R[2]C[/B][" & tmp ...

whereas in your second you have;

ActiveCell.FormulaR1C1 = "=SUM('data'![B]RC[/B][" & tmp ...

Try changing to;

ActiveCell.FormulaR1C1 = "=SUM('data'![B]R[1]C[/B][" & tmp ...

: D

Re: Linking Cells

You could try putting the line(s) within a loop;

For tmp = 7 To 210 Step 7


    Range("C3").Select 
     
    ActiveCell.FormulaR1C1 = "=SUM('data'!R[2]C:R[79]C)" 
     
    ActiveCell.Offset(0, 1).Activate 
     
    ActiveCell.FormulaR1C1 = "=SUM('DE PL'!R[2]C[" & tmp & "]:R[79]C[" & tmp & "])" 


Next tmp

: D

Re: Refreshing shared workbooks

Not that I know of. I've used this method on a number of projects. You could have a peice of VBA that checks the timestamp of the file to see if anyone has saved since you had last checked, however this probably adds as much overhead as it saves, and adds a lot of complication. : D

Re: populating list box.

Can you upload an example?

Re: Userform Button Condition

in that line, you basically say;

"X or Y and Z"

This = "X or (Y and Z)"

What you need is "(X and Z) or (Y and Z)", so try:

If Left(txtDifference.Text, Len(txtDifference.Text) - 1) > 10 And txtAddInfo = "" Or Left(txtDifference.Text, Len(txtDifference.Text) - 1) < (-10) And txtAddInfo = "" Then

Hope this helps

Scott : D

Re: Copy Column & Paste to Last Used Column

No probs

Re: Copy Column & Paste to Last Used Column

OMG! I think I've aged about 10 years!!! : D

Finally got it working though.

I figured out what was up with that error message you were getting. In my example, in Tabelle2, A1:A4 had data in it(to make sure that we didn't overwrite it). However, this meant I had failed to test it for the first 2 search results, which would get pasted into Columns A & B respectively.

If you use '.End(xlToRight)' on an empty range, or one with only 1 cell, then it jumps to the end of the row, rather than the last used cell. Therefore we need 2 special cases, 1 for if this is the 1st result (i.e. A1 is blank) and one for the 2nd result (i.e. B1 is blank).

I fixed this, and then started getting a wierd error - runtime error 9 (Subscript out of range). I managed to track this to the following line;

With Sheets("Tabelle1")

So I moved the sub 'Search' into the main Workbook module ("ThisWorkbook" aka "DeiseArbeitsmappe"). Prepend each call with 'ThisWorkbook.", and we're set;

[HR].[/HR]

In Tabelle1;

Private Sub CommandButton1_Click()


ThisWorkbook.Search ("SLOT_0_KARTE")
ThisWorkbook.Search ("SLOT_0_DIENST")
'...


End Sub

In DeiseArbeitsmappe;

Sub Search(ByVal strSrch As String)


    Dim xFind, z As Integer
    Dim rnge As Range
     
     'MsgBox "in Suchfunktion !"
     
    If strSrch = "" Then Exit Sub
     
    With Sheets("Tabelle1")
        Set xFind = .UsedRange.Find(What:=strSrch, After:=Range("A1"), _
        LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)
    End With
     
    If Not xFind Is Nothing Then
         
        With Sheets("Tabelle2")
            If .Range("A1").Value = "" Then
                Set rnge = .Range("A1")
            ElseIf .Range("B1").Value = "" Then
                Set rnge = .Range("B1")
            Else
                Set rnge = .Range("A1").End(xlToRight).Cells(1, 2)
            End If
            xFind.EntireColumn.Copy Destination:=rnge
        End With
         
    Else
        MsgBox "Suchbegriff wurde nicht gefunden !"
    End If
     
End Sub

If this doesn't work now, I'll end up bald from pulling my hair out! : D

Re: Copy Column & Paste to Last Used Column

Click on 'post reply', and look for the 'Manage Attachmemts' button near the bottom.

Re: Copy Column & Paste to Last Used Column

Hmm, that's strange, it is working fine for me. Can you attach your workbook and I'll have a look? : D

Re: Copy Column & Paste to Last Used Column

Hope you don't mind, but I've tweaked this a little. I was getting an error by using 'Cells.Find', so opted to use 'UsedRange.Find' instead. I also noticed you had set 'After:=ActiveCell'. This will cause the sub to only find results that appear after the last result, so if you search for something earlier, it wont find it.

Sub Search(strSrch As String)
     
    Dim xFind, z As Integer
    [B]Dim rnge As Range[/B]
     
    'MsgBox "in Suchfunktion !"
     
    If strSrch = "" Then Exit Sub


    With [B]Sheets("Tabelle1")[/B]
        Set xFind = .[B]UsedRange[/B].Find(What:=strSrch, After:=[B]Range("A1")[/B], _
                               LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByColumns, _
                               SearchDirection:=xlNext, MatchCase:=False)
    End With


    If Not xFind Is Nothing Then


        With [B]Sheets("Tabelle2")
            Set rnge = .Range("A1").End(xlToRight).Cells(1, 2)[/B]
            xFind.EntireColumn.Copy Destination:=[B]rnge[/B]
        End With


    Else
        MsgBox "Suchbegriff wurde nicht gefunden !"
    End If


End Sub

Re: Copying And Pasting Onto Rows

Can you attach an example

Re: Survey Talley-Multiple (5) Choices

Ok, sure. What is "% OF EE'S WHO ANSWERED"?

Scott : D

Re: Option button Basic formula?

Rather than radio buttons, why not use the cells as radio buttons (as in http://www.ozgrid.com/forum/showthread.php?t=10870). I have adapted it slightly so that double-clicking one cell clears the others in that row. See the attached version of your example.

Hope this helps : D

Re: Survey Talley-Multiple (5) Choices

Hi Kathryn,

What I mean is;

- Who will be completing the survey - you or your participants.

If it is your participants, then all you need is for one of the options to be completed for each question

If this is for you to compile results, and you want each option box to contain the number of times that option was selected, then that is different.

I wanted to know what ou are trying to do or what you need this for.

Scott : D

Re: Survey Talley-Multiple (5) Choices

Hi,

I'm not sure what you are trying to do here.

What are you trying to acheive?

Scott

Re: Creating Graphs With Source Data On Multiple Tabs

I've mocked up an example (attached) which assumes a few things;
- each daily tab is named dd-mm-yy, eg 03-11-06
- Cells in B1 & B2 of each sheet are of interest to us

I find it easier to collate the information onto 1 sheet first, then chart this data.

I use a table on 'Sheet1'

Column A contains the dates, which correspond with the names of the data Sheets. Enter the earliest date in A2. Dates in the future are left blank by the following formula in A3 (then use fill down);

Quote

=IF(A2="","",IF(A2+1>TODAY(),"",A2+1))

Each of the data Sheets has data in Cells B1 (Value X) and B2 (Value Y). Sheet1 Column B contains all the X's, and Column C contains all the Y's. The formula for Sheet1, B2 is;

Quote

=IF(A2="","",INDIRECT("'"&TEXT(A2,"DD-MM-YY")&"'!B1"))

and for C2;

Quote

=IF(A2="","",INDIRECT("'"&TEXT(A2,"DD-MM-YY")&"'!B2"))

Fill these down as well, and this will pull through all the data onto 1 sheet.

[HR] . [/HR]

The next thing is to chart the data. I prefer to name the ranges used for each series. In this case, where each series grows daily, dynamic named ranges are useful.

I used the named ranges;

Quote

Dates: =INDIRECT("Sheet1!$A$2:$A$"&COUNT(Sheet1!$A:$A)+1)
ValX: =INDIRECT("Sheet1!$B$2:$B$"&COUNT(Sheet1!$A:$A)+1)
ValY: =INDIRECT("Sheet1!$C$2:$C$"&COUNT(Sheet1!$A:$A)+1)

Add a new Chart. The Category Axis should be =ChartingEx1.xls!Dates. Add each series;
- Series X: =ChartingEx1.xls!ValX
- Series Y: =ChartingEx1.xls!ValY

As each cell in Column A fills in (ie 'IF(A2+1>TODAY()' becomes False), the Indirect function looks for that sheet, and pulls through the appropriate value. Each Dynamic range updates, and becomes 1 cell longer, and the Chart should then update itself.

[HR] . [/HR]

Hope this helps

Scott : D