Free Microsoft Excel 2013 Quick Reference

how to prevent vba code from changing cell formulas?

I have a sheet with some formulas (sheet 1) that reference another sheet (sheet 2) that is updated by vba code that does some math based on a few other sheets. The vba adds and deletes rows and columns (on sheet 2) as it processes the code. After running the vba, the absolute equations on sheet 1 (that referenced sheet 2 that was updated by the vba code) are changed; the columns and rows are moved over and up depending on the number of times i run the code.

What is causing this to happen? What can i do so that my vba does not affect my formulas on the other sheet? Any help or insight would be greatly appreciated. Thanks all!

Dave


Post your answer or comment

comments powered by Disqus
In my Access database (mdb), there's one query which have some fields being changed per Week#. I setup one MS query in Excel to let my users to retrieve all the fields into Excel. I change the SQL of Excel query like "Select * From ..." where * means all the fields are needed, but Excel always change the * to detailed fields names which need me change the Excel Query every week because the week# are changed with time-being.

How to prevent MS query from changing the * to detailed fields name?

Thanks,
Tim

how to read a value from a cell in VBA?

I use this code for fill the cells by color :

Code:
so how to delet the color from the cells 

by the way I use this code Cells.ClearFormats but it delet all format
I want only color
NAd thank you for help

how to prevent MS query from changing the * to field names?
In my Access database (mdb), there's one query which have some fields being changed per Week#. I setup one MS query in Excel to let my users to retrieve all the fields into Excel. I change the SQL of Excel query like "Select * From ..." where * means all the fields are needed, but Excel always change the * to detailed fields names which need me change the Excel Query every week because the week# are changed with time-being.

How to prevent MS query from changing the * to detailed fields name?

Thanks,
Tim

I have several vba code files (*.vba, *.frm).
How can I add, not import, them to another project using VBA coding
just like insert API functions? Am I clear?

BTW, how to using vba code to add reference (like solver)?

Thanks

--
Message posted from http://www.ExcelForum.com

How to prevent the text from cells to spill over adjacent cells? I know i can use "wrap text" but it changes the height of the cells and i don't want to change the cells size. I just want to hide the text that is over the cell border.

Thanks!

Hello microsoft.public.excel.misc my name is Zig I am trying hard to
find out how I can use a input application to input data into a
specified excel sheet. I have learned how to input the data into the
excel sheet although I need to prevent the inputs from being
duplicated. I have used a validating solver to prevent duplicate
entries but it only works if you type the text into the sheet. I am
unable to get the input application to follow the validation rule. Is
there a way that I can use a input application to prevent duplicate
entries into the excel sheets and if duplicate data is true can i
redirect it to another specified sheet? Please help! If you can help
please email me @ Zigball @ Gmail.com

This is a code that I use to get a input into the spreadsheet.

' frmAddresses class
Option Explicit
Private Sub UserForm_Initialize()

'Load the combobox with states.
cmbStates.AddItem "AL"
cmbStates.AddItem "AR"
cmbStates.AddItem "AZ"
cmbStates.AddItem "CA"
cmbStates.AddItem "CO"
cmbStates.AddItem "MD"
cmbStates.AddItem "NC"
cmbStates.AddItem "NY"
cmbStates.AddItem "WV"

End Sub

Private Sub txtZip_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)

' Pass through only digits.
If KeyCode < 48 Or KeyCode > 57 Then
KeyCode = 0
Beep
End If

End Sub

Public Function ValidateData() As Boolean

' Returns True if the data in the user form
' is complete, False otherwise. Displays a
' message identifying the problem.

If txtFirstName.Value = "" Then
MsgBox "You must enter a first name."
ValidateData = False
Exit Function
End If
If txtLastName.Value = "" Then
MsgBox "You must enter a last name."
ValidateData = False
Exit Function
End If
If txtAddress.Value = "" Then
MsgBox "You must enter an address."
ValidateData = False
Exit Function
End If
If txtCity.Value = "" Then
MsgBox "You must enter a city."
ValidateData = False
Exit Function
End If
If cmbStates.Value = "" Then
MsgBox "You must select a state."
ValidateData = False
Exit Function
End If
If txtZip.TextLength 5 Then
MsgBox "You must enter a 5 digit zip code."
ValidateData = False
Exit Function
End If

ValidateData = True

End Function

Public Sub ClearForm()

'Clears all data from the form.
txtFirstName.Value = ""
txtLastName.Value = ""
txtAddress.Value = ""
txtCity.Value = ""
txtZip.Value = ""
cmbStates.Value = ""

End Sub

Public Sub EnterDataInWorksheet()

'Copies data from the user form
'to the next blank row in the worksheet.

Dim r As Range, r1 As Range

Set r = Worksheets("Addresses").Range("A2").CurrentRegion
Set r1 = r.Offset(r.Rows.Count, 0)
r1.Cells(1).Value = txtFirstName.Value
r1.Cells(2).Value = txtLastName.Value
r1.Cells(3).Value = txtAddress.Value
r1.Cells(4).Value = txtCity.Value
r1.Cells(5).Value = cmbStates.Value
r1.Cells(6).Value = txtZip.Value

End Sub

Private Sub cmdCancel_Click()

ClearForm
Me.Hide

End Sub

Private Sub cmdDone_Click()

If ValidateData = True Then
EnterDataInWorksheet
ClearForm
Me.Hide
End If

End Sub

Private Sub cmdNext_Click()

If ValidateData = True Then
EnterDataInWorksheet
ClearForm
End If

End Sub

Hi,
I have a protected worksheet in which some cells are locked and some cells are unlocked for data entry. On the updateable cells, the user can type in data, as well as paste data by copying other cells (ideally from the same worksheet, having similar formatting).
The problem is: the user is able to copy data from other worksheets, with potentially different formatting, and then paste it on my worksheet. This messes up the formatting on my worksheet.

So, my requirement is that I want the Paste operation to only paste values, and not formatting. This works fine if the user uses 'Paste Special' > Values. But I want to force this behaviour, even when the user does a Paste or uses Ctrl-V. Any ideas?

Thanks,
Sam

We have a large system done in Excel 4 Macro Language. We would like to
start migrating to VBA by writing all future additions and modifications in
VBA.

Is there a way to call a VBA function from an Excel 4 Macro? If so, how do
you do it, how do you pass parameters to the VBA function, and how do you get
a return value from the VBA function?

Hi all,

my problem is this:

1'st I'm an Excel noob, haven't really used any advanced features.

2'nd I need to copy specific information from a cell that has a lot of other text that's not needed in it.

3'rd This has to be done several thousand times.

f.ex. I've got a thousands of cells with: "Product name, compatability, product number, product dimensions, product specifications". How do I copy "compatability" on all these to a separate cell for each of the "compatability" automatically? I guess I'll have to use a macro, but does anybody know how I should code this?

Would greatly appreciate any help.

Best regards
Thomas.

I have an Excel workbook that will be used by multiple users who share a network drive. I would like to set it up so that the VBA code is ran from a text file stored on the network drive containing the VBA code. Basically a macro would run from the Excel workbook and get the VBA code from the text file and then run that code. Has anyone done this before or can anyone provide the syntax for this process?

Hello!
I installed Excel 2003 recently. There are short cuts in tool tips in Excel
2003. How to prevent the short cuts from disappearing from the tool tips
during a
service pack installation? How to return short cuts to tool tips in Excel
2003 sp3?
Regards,
Dmitry

Hi,

Can anyone teach me how to prevent other users from printing in Microsoft
Excel and in Microsoft Word?
So far, I know Adobe Acrobat only can do this.

Thank you!

I am working on a VBA application for Excel that should work in Windows versions 2000 thru 2010, and Mac 2004.

In my dBase3+ and FoxBase+ days, I would create and store a line of code to a variable based on If...Else...EndIf conditions and then execute the code via the variable. For example:


	VB:
	
 County = "KERN" 
mIndex = "Index On Well_Number For County = "KERN"" 
Else 
    mIndex="" 'for do nothing
    EndIf 
     
     'the first character "&" means execute code stored in mIndex
    &mIndex 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can this be done in VBA to get around compile errors?

First example is for showing a Userform Modeless-Model which Mac Excel hates (ie, compile error)


	VB:
	
 
    mUserform = "Userform1.Show" 
Else 
    mUserform = "Userform1.Show vbModeless" 
End If 
 
&mUserform 'variable to execute the code in mUserform

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The second example is to deactivate the autosave and-or autobackup setting. Mac Excel hates ThisWorkbook.EnableAutoRecovery (compile error: method or data member not found)


	VB:
	
 
    mSaveSet = "Application.SaveInterval = 0" 
Else 
    If application.version = "9.0" Then 
        mSaveSet = "Application.AddIns("Autosave Add-in").Installed = False" 
    Else 
        mSaveSet = "ThisWorkbook.EnableAutoRecover = False" 
    End If 
End If 
 
&mSaveSet 'variable to execute the code in mSaveSet

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Again, I want to save VBA code to a variable based on condition(s) for later execution, and to avoid compiler errors

Hello Experts,

Is there a way to prevent a macro from one workbook to execute in another workbook? For example, I have "Macro1" recorded in workbook A. Can I block this macro not to execute in workbook B?

Thanks,

Yuki

hi guys.

I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem.

My worksheet runs a lot of code, then deletes all macro code and saves itself.

The problem is the next time i open I still get the macro security warning!

I've checked thoroughly and there is definatley no vba left anywhere.

If I open the document, enable macros, and save it, then open it again, I no longer get the warning. verrry frustrating.

Hi, is there a way to put vba code to a standard alone exe file? This way, I don't have to keep update the code on different excel files.

Thanks!

Hello All ,

I would like to know how to write VBA code to copy a specific range in Excel and then save it in .JPG format ?

Please suggest.

Good day,

Does anybody know a way to call VBA code from html or javascript?

Thanks,

Hi AllCould you please help me the way to writing VBA code in Addnew Button in Access 2003.

My apologies for the poor format on my previous thread.

My goal is to input code to add a blank row between pre-existing rows with data. I have found some code to do the task - on a previous thread - but I lack the knowledge and experience in how to input the code.

Could someone help me, or am I trying to do something only a programmer is capable of?

Thank you.

Here is the code:
Sub InsertBlankRows()
Const Rows2Insert As Integer = 1

Dim lngCtr As Long
Dim lngRows As Long
Dim iStep As Integer

iStep = Rows2Insert + 1
lngRows = iStep * (ActiveCell.End(xlDown).Row - ActiveCell.Row)

On Error GoTo errTrap
For lngCtr = 1 To lngRows Step iStep
   ActiveCell.Offset(RowOffset:=lngCtr).Resize(RowSize:=Rows2Insert).EntireRow.Insert
Next lngCtr
Exit Sub
errTrap:
   MsgBox _
      Title:="Incomplete Action", _
      Prompt:="Cannot continue inserting rows", _
      Buttons:=vbCritical + vbOKOnly
End Sub


What is a smart way to mask unnecessary spaces from a cell. The problem exists if you are summarizing cell values and one of the cells contains space(s) after/before the digits.
Leevi

Hello,

I have a macro that needs access to files so I have to specify the folder path. So I usually have to go to the VBA code and update the folder path, e.g.:

Folder = "C:Documents and SettingsJane.DoeDesktopTESTING"

However, I am trying to make this Excel workbook more user friendly so other uses can use it too. So I am planning to create an New Tab where the user inputs important data that the macro needs before pressing a 'Run Macro' button. For example:

The user is asked to input the folder path in cell B1, and the user simply enters: C:Documents and SettingsJane.DoeDesktopTESTING

The question is, how do I make the VBA code get the folder path from cell B1? That is, how do I make Folder = "B1" of New Tab?

---

Similarly, the VBA code has a line that says:

Application.Workbooks.Open ("C:Documents and SettingsJane.DoeDesktopTESTINGEngineering_worksheet.xls")

The file name will not change, i.e.: Engineering_worksheet.xls, but the folder path will change, i.e.: C:Documents and SettingsJane.DoeDesktopTESTING

So how would I write the above VBA code so that it works properly by obtaining the folder path from cell B1 of New Tab, but keeping the file name the same?

Thanks a lot and I am looking forward to your feedback

I would greatly appreciate any help with this, Im sure some of you will see my error right away.

I am trying to create a program to automate the gauging figures on oil barges, currently everything is done by hand and takes approximately 20 minutes to complete. I have the charts entered for the tanks already and have the code set so that when you click on a tank "gauge" it will enter the "volume" which corresponds to that gauge on a totals sheet.

Here is where my problem is coming in

The barges are gauged at four points
"before loading"
"after loading"
"before discharge"
"after discharge"

I want to set a worksheet as the default page with four command buttons that let the user select which operation he wants to perform. ie
before load figures
after load figures

and depending on which operation they select have it enter the volumes in the appropriate cells on the totals sheet.

So if someone selects "loadopen" command button I want the following code to run on my worksheets.

==========================================================

	VB:
	
) 
    cancel = True 
     
    oneportvolume = Target.Value 
    Sheet11.Range("D10") = oneportvolume 
    Sheet11.Range("G10") = Target.Offset(0, 1).Value 
    MsgBox (Target.Value) 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
==========================================================

and if they select the "loadclose" command button I want this code to run

	VB:
	
) 
    cancel = True 
     
    oneportvolume = Target.Value 
    Sheet11.Range("D27") = oneportvolume 
    Sheet11.Range("G27") = Target.Offset(0, 1).Value 
    MsgBox (Target.Value) 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
==========================================================
now the only thing that has changed are the cells into which the number will be placed.

So how do I accomplish this, "i've tried IF then statements without success" I probably just did not set it up correctly

My other question is about the variable "oneportvolume" do I need to change the variable also to prevent the code from overwriting the previously entered number?

Sorry if this was too long or too much info. If needed I will try to shorten the questions

Thanks so much to anyone willing to help me


No luck finding an answer? You could always try Google.