Posts by jamesparker_1

Hi there.

I've been stuggling on this one for a while so hopefully someone can help:

Basically, I have a table of data (see attached file) and I have created a graph of that data. I need to find out the equation of that graph but the trendlines in excel don't seem to fit. Is there any way to determine the exact equation of the line either through the graph or the raw data table?

Thanks

James

Re: Send Email If Cell Value Changes

Hi Sicarii

Thanks for your help. Yeah, I would appreciate help getting the update code. At the moment the web stuff is just a web query so it updates through that. How do I make a macro that monitors the cell value?

Thanks

James[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Ah,

just found the SheetChange function! Thanks for your help! It got me looking in the right places!

James

Hi All,

I have a spread sheet containing some share price data that updates automatically from the web.

I also have my Microsoft outlook open most of the day on the same PC.

What I would like is for my spreadsheet to email me if the share price rises past a certain value.

Any Solutions?

Thanks

James

Re: Attachment Not Attaching In Lotus Notes

Hi,

Site_Path seems correct.... It is defined further up in the code (it's quite long code) I have added a msgbox in the code to display Site_Path at the point the attachment should happen and it shows the correct files path and name(eg "c:\Test\Filename") although it doesnt have the ".xls" on the end. Is this important?

I did however notice that I had Written the below code but had used Attachment in some parts which I have now, as shown below, changed to Attachment1. It didnt seem to solve the problem though.

Attachment1 = Site_Path
                If Attachment1 <> "" Then
                    Set attachME = MailDoc.CreateRichTextItem("Attachment1")
                    Set EmbedObj1 = attachME.EmbedObject(1454, "", Attachment1, "Attachment1")
                    MailDoc.CreateRichTextItem ("Attachment1")
                End If

Other than that I am still stuck as to what the problem is.

Thanks

James

Hi All

I have a problem getting an attachment (variable Site_Path) to attach to an email sent out in lotus notes. I found the below code while searching this forum but when I use it the email sends correctly except for the fact that the excel file i'm sending isnt attached. I have tried debugging and Site_Path contains the path to the file and the filename itself which I assume is correct. So I guess either I have copied the code incorrectly or the Variable Site_Path is incorrect?

Is anybody able to help? Here is the code.

'************************************************************************************************************
            'Emails Site File to chosen Recipients in lotus notes


            If Select_Sites_Separation.Mail_CheckBox.Value = True Then


                ' setting up various objects
                Dim Maildb As Object
                Dim UserName As String
                Dim MailDbName As String
                Dim MailDoc As Object
                Dim attachME As Object
                Dim Session As Object
                Dim EmbedObj1 As Object
                Dim recipient As String
                Dim ccRecipient As String
                Dim bccRecipient As String
                Dim subject As String
                Dim bodytext As String
                Dim Attachment1 As String
                'Dim Site_Path As String
     
                ' setting up all sending recipients
                recipient = Data.Range("D9").Value
                ccRecipient = Data.Range("E9").Value
                bccRecipient = Data.Range("F9").Value
                subject = Data.Range("D21").Value
                bodytext = Data.Range("D22").Value & _
                            vbNewLine & _
                            Data.Range("D23").Value & _
                            vbNewLine & _
                            Data.Range("D24").Value
     
                '// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text
                If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then
                    MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation
                    Exit Sub
                End If
     
                'creating a notes session
                Set Session = CreateObject("Notes.NotesSession")
                UserName = Session.UserName
                MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
                Set Maildb = Session.GETDATABASE("", MailDbName)
     
                If Maildb.IsOpen <> True Then
                    On Error Resume Next
                    Maildb.OPENMAIL
                End If
     
                Set MailDoc = Maildb.CreateDocument
                MailDoc.Form = "Memo"
     
                ' loading the lotus notes e-mail with the inputed data
                With MailDoc
                    .SendTo = recipient
                    .copyto = ccRecipient
                    .blindcopyto = bccRecipient
                    .subject = subject
                    .Body = bodytext
                End With
     
                '  saving message
                MailDoc.SaveMessageOnSend = True


                Attachment1 = Site_Path
                If Attachment1 <> "" Then
                    Set attachME = MailDoc.CreateRichTextItem("Attachment1")
                    Set EmbedObj1 = attachME.EmbedObject(1454, "", Attachment1, "Attachment")
                    MailDoc.CreateRichTextItem ("Attachment")
                End If
     
                ' send e-mail !!!!
                MailDoc.PostedDate = Now()
                ' if error in attachment or name of recipients
                On Error GoTo errorhandler1
     
                MailDoc.Send 0, recipient
     
                Set Maildb = Nothing
                Set MailDoc = Nothing
                Set attachME = Nothing
                Set Session = Nothing
                Set EmbedObj1 = Nothing
     
                'Unload Me
                'Exit Sub
                ' setting up the error message
errorhandler1:
                MsgBox "Incorrect name supplied or the attachment has not attached," & _
                "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _
                "to ensure the application runs correctly and that a vaild connection exists"
     
                Set Maildb = Nothing
                Set MailDoc = Nothing
                Set attachME = Nothing
                Set Session = Nothing
                Set EmbedObj1 = Nothing
                ' unloading the userform
                'Unload Me


            End If


'*********************************************************************

Thanks in advance for your help!!

James

Re: Find And Replace Only If Cell Is Not Blank

Hi Ian

Yeah,That works great! Many thanks for the help!!

James

Hi Everyone,

I'm still getting to grips with the Find Command. Basically, I have the below code that looks at a line in a sheet(ws_Site) based on the Line ID Number that is in column A and then Finds that that LineID_Value in a different sheet(ws_main) and copies a value from column I on that line across. Problem is, if the cell I is blank it copies it across and essentially clears the cell if there was data in it.

How can I adjust the below code to only set ws_main.Range(c.Address).Offset(0, 8).Value = LineID_Value if LineID_Value is not blank?

For Each LineID In ws_Site.Range("A7:A" & SiteRows) 'Loop through all the cells in range 


          LineID_Value = LineID.Offset(0, 8).Value
            
                    'Dim c
                    With ws_main
                        Set c = .Range("A7:A3000").Find(LineID, LookIn:=xlValues)
                        'If Not c Is Nothing Then
                        
                            ws_main.Range(c.Address).Offset(0, 8).Value = LineID_Value
                        
                        'End If
                    End With


            Next LineID

Thanks

James

Re: Hide Password Text

Ah Great! Thanks Guys! I knew there must be a way!

Many thanks for your help!

James

Re: Stop Close Form Button Working On Userforms

Ah, Brilliant!

Yes, All those variations work perfectly!

Thanks you very much guys for your expert assistance!

James

Hi All,

I have a useform that loads up with two buttons and I waat to force the user to chose one of the buttons. However, I have noticed that the user can just press the cross in the top right corner of the form to close the window and therefore not press either button.

Is there anyway I can stop the close form button working?

Thanks

James

Hi All,

I have a userform that loads up and requests a password before loading another userform full of administrator controls. I would like the text box to * out every character of text as the user types it in so that the password only appears as ******** in the text box.

Is this possible? My code is below:

Private Sub OK_Password_Button_Click()


'Asks for the administrators password and allows access to the options if password is correct


If Password_Admin.Value = "password" Then  'If password is correct...
    
    Unload Me
    Load Administrator  'Load administrator controls
    Administrator.StartUpPosition = 2
    Administrator.Show


Else


    MsgBox ("Password is Incorrect")    'If password is incorrect...


End If


End Sub

Thanks

James

Re: Initialize Checkbox Name

Ah, Glad it was something simple!

Great Stuff, thanks very much for your help.

Data is the Codename of the sheet rather than the sheet Tab name.

Thanks again for your help!!

James

Hi All,

I want to load a userform with 2 checkboxes. I want the Checkboxes text to equal whatever is in two specific cells on my spreadsheet. i.e. I want to initialise the checkbox text to be the vale of cell A1 and A2 on sheet 1 when I load the userform. To load my userform I am using

Sub Prepare_Sites()
'
    Application.ScreenUpdating = False


    Load Select_Sites_Separation    'Loads up userform
    Select_Sites_Separation.StartUpPosition = 2
    Select_Sites_Separation.Show


    Application.ScreenUpdating = True


'
End Sub

and then I thought on the userform I would use the following private function but this isnt working.

Private Sub UserForm_Initialize()


Bmth_CheckBox.Name = Data.Range("A9").Value
Dub_CheckBox.Name = Data.Range("A10").Value
     
End Sub

Can anyone tell me what i'm doing wrong.

Thanks

James

Hi All

I have a macro that hides a row if a cell in that row is blank. The VBA code is shown below.

With Edin_Cap
        .Range("B26:B36").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        .Range("B44:B86").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        .Range("B93:B124").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    End With

I have discovered that this formula becomes a problem where a cell might contain a formula but no value. For example, if the cell contains the formula:

=IF(ISBLANK('Template'!B78),"",'Template'!B78)

if the above value is "" then the 'hide entire row' macro doesnt work. How can I hide a row if the cell VALUE is blank?

Thanks

James

Re: Copy Formula To New Worksheet Without Path

Hi Aaron!

Thanks so much! That did exactly what I needed!

Very much appreciated!

RoyUK, Yes this will be something i will want to do regularly. I've never used templates before but looking at the helpfile I guess you are saying that I can setup standard formula use them again and again? If so then I think that looks like something I shall look in to!

James

Re: Copy Formula To New Worksheet Without Path

Hi Aaron!

I hope you had a good break over easter! Thanks for looking at the above problem!

Yeah, unfortunately I am still stuck and it looks like nobody has been able to offer a solution. So if you are able to help me then I would greatly appreciate that!

Thanks

James

Re: Worksheet Name To Variable

Hi Kenneth

Great stuff! Thanks! That really works great!

Thanks so much for your help!

James

Re: Copy Formula To New Worksheet Without Path

Hi Aaron

I want the formula to just copy across as it is written because I have the same sheet names in the workbook i am copying it across to.

so for example, if the formula in the cell is:

I.e. ='Add Detail Client Delivery-Bmth'!C6

I want to copy it to be exactly the same in the new workbook. I.e.

I.e. ='Add Detail Client Delivery-Bmth'!C6

I hope that makes sense.

James

Re: Error Selecting Sheet In Opened Workbook

Hi Guys, Your suggestions and code worked great!!

Thanks ever so much. I'm having a few minor problems converting it to suite my workbook aand so have posted a couple of new threads but the help has been really great!

Thanks for your time!

James

Re: Copy Formula To New Worksheet Without Path

Hi PetLahev

Thank you for your reply. It is very much appreciated.

However, I don't think that will work because I have many formula and many data values so I would have to specify each cell i think.

Is there anyway of just pasting the sheet in one go?