you could try this Application.DisplayAlerts = False This needs to be entered before your statement xl.ActiveWorkbook.SaveAs(strFileName)

Hi folks I am using Visual Basic script and I need to select a range based on one of the following notation Worksheets(SetMonth).Range($D$8:$D$24).Select StartLocation = "$D$8" EndLocation = "$D$24" Worksheets(SetMonth).Range(" & StartLocation & ":" & EndLocation & ").Select Both of the above seem to work with other formulations however I don't seem to be able to use either of the above to select the range of cells required. Can Anyone help

Hi folks I have a dilema I run a script from a button which deletes a certain piece of information, the script works fine however if the row of data that is removed has a button anywhere on the subsequent row, the button is resized is there anyway of getting the button to stay its original size. is there anyone out there that can help me it would be appreciated

cheers for that

Hi I am using the following command Sheets("SHEETS").Delete this works however it requires the user to confirm deletion, is there another way of deleting a sheet without the user confirming Thanks

Hi I have the following script Dim DStartR As String Dim DStartC As String Dim DEndR As String Dim DEndC As String Dim StartLocation As String Dim EndLocation As String Worksheets(SetMonth).Range("D5").Select DStartR = "=Cell(""row"",RC)" 'ROW ADDRESS OF D5 DStartC = "=Cell(""row"",R[-1]C)" 'COLUMN ADDRESS OF D5 Worksheets(SetMonth).Range("E5").Select DEndR = "=Cell(""row"",RC)" 'ROW ADDRESS OF DEndC = "=Cell(""row"",R[-1]C)" How do I combine the variables DStartR and DStartC to give me a variable called StartLocation and variables DEndR and DEndC to give me a variable called EndLocation I then need to generate a calculation using these points which will update as their contents change. I was using a For LOOP to generate a Variable called DTOTAL, however once the script had finished, it wouldn't update when D5/E5 changed, as a result I need to generate a sum at a location that could potential change based on various generated information. ie run1 of the script could put DTOTAL at cell reference A22, however run2 of the script could put DTOTAL at cell reference A23 etc. as a result I need to use the following line of code I think ActiveCell.Value = "=sum(" & StartLocation &":" & EndLocation &)" I am unable to check the above as I cant' figure out how to generate StartLocation and EndLocation I am going to carry on trying to figure out how to do what I need to do, however if some1 can give me a heads up, it would be appreciated Thanks In Advance

Hi Folks I am Using Visual Basic within Excel and have encountered a problem I hope someone can help with I have a table of Bills and months ie Month1 Month2 ........ Bill 1 0 0 Bill 2 0 0 Bill 3 0 0 For each bill there is a value in the month column. Each Cell starts with a value of 0 I create a NEW worksheet ie - NEWSHEET and this passes a formula back to cell C3 on sheet Bills C3 before = 0 C3 after = NEWSHEET!L5 - showing the contents of Cell L5 ON Sheet NEWSHEET When I delete the NEWSHEET for whatever reason the value of C3 changes to this:- C3 becomes =#REF!L5 In itself this isn't a problem as I was expecting this to happen however once the cell contains =#REF!L5 I want to run a small script to look at each cell and if the value of the cell is =#REF!L5 then I want to reset the value to zero if the value of the cell isn't =#REF!L5 then I wish to leave the value alone Hope this makes sense and that someone can assist

[Message Deleted]

Hi I have two locations referenced by point1 = A5 point2 = A10 I need to use these variables in a calculation ie =sum(point1:point2) however this doesn't work? when I use this statement ActiveCell.Offset(0, 3).Value = "=sum(point1:point2) any work around / solution? if you require additional info let me know?

yes it is

I need to find out how to do the following: I have a script to do this Ask User for a variable Create a NEW sheet with the variable name I am able to do some of the above however I need to incorporate some error checking to prevent the script failing. wats the best course of action hope this makes sense to someone

I am trying to get a variable to pass to another procedure. I have set my procedure as follows Sub Procedure(X) ' programming code End Sub Normally I would use the following to send data Procedure1 Count6 however in this case I am running the called procedure using this code With ActiveWorkbook.Sheets(SetMonth).Buttons.Add(600, 20, 100, 25) .OnAction = "Procedure1" .Caption = "Bills Not Paid July" .Name = "JULY" End With how do I adapt the ".OnAction = "Procedure1" section to pass the variable" Can anyone help me

Hi Private Sub Month_Click() Worksheets(SetMonth).Range("M1").Select Count6 = 0 Do Count6 = Count6 + 1 ActiveCell.Value = "0" ActiveCell.Offset(1, 0).Activate Loop Until Count6 = Count5 need to pass Count6 to Procedure1 Sub Procedure1() SetMonth = "August" Count7 = 0 Do Worksheets(SetMonth).Range("M1").Select Bill1 = Application.InputBox("Enter July Amount?") ActiveCell.Value = Bill1 ActiveCell.Offset(1, 0).Activate Count7 = Count7 + 1 Loop Until Count7 = Count6 Worksheets(SetMonth).Range("A:K").Select Worksheets(SetMonth).Columns("A:K").AutoFit End Sub Does this mean then that I change the following or am I misunderstanding, its been a while since I wrote visual basic script: Private Sub Month_Click(ByVal Count6 As String) and Sub Procedure1(Count6 As String)

I need to pass a variable from one procedure to another in visual basic, can anyone help me by sending me a small example

Can some1 remind me how I pass a variable from one procedure to another in excel visual basic

Is it possible to do the following and if so how? 1) assign variable a range ie: Range = "A1" 2) perform calculations with the variable ie: NewRange = Range + 1 answer = B1

:) your a star, you was right.

doesn't for me :sigh: I get a message saying The macro 'Finance2.xls!FunctionName' cannot be found. when I click on the button

Cheers exactly what I needed I have figured out how to create the button, and place on the screen at the desired location, and I have created the script I want the button to perform. Brief example of my layout Private Sub Month_Click() With ActiveWorkbook.Sheets(SetMonth).Buttons.Add(500, 50, 100, 25) .OnAction = "FunctionName" .Caption = "Foreign" .Name = "cmdNewButton" End With End Sub Sub FunctionName() Amount1 = Application.InputBox("Enter Amount") Worksheets(SetMonth).Range("A1").Value Amount1 End Sub The problem is it won't work, any ideas as to what I am doing wrong?

this allows me to create a button to run the script but how do I write code to create the button within a currently running program. example I am currently running a script to create a NEW worksheet from scratch and then display certain information based on data the user enters. I want the script to then create a series of button on this NEW worksheet to allow the user to then modify values that they have already entered. As a result I need to hardcode creating the button into initial script, and then add the various code to the button to allow them to update values. Hopefully this makes sense and someone can help me with my dilema