Posts by Blur2037

Re: Insert A Date into Cell

Yep, thats exactly what I need. Thanks guys

I dont know the code that I need to insert a date, not just Date(), into a cell in the format of "m/d/yyyy" I know its something really simples but I still need it.
thanks for the help

Re: Differentiate Between True & False Cells

i misunderstood you and i know that it was confusing norie. i replied to you on the other post. i am confused with these spreadsheets myself. i am new to them. sorry that i was somewhat rude. they were very frustrating with the properties and conditioning of the cells. all finished.

Re: Alter Each Cell Individually True/False

i did not know what you meant by late. sorry. i didnt look at what time you posted. my bad. its cool tho i finally got it to work by changing the false value to "0.00%" and then by testing if it is text or number. thanks for your help tho.

Re: Differentiate Between True & False Cells

Thanks for your help. I tried doing this before with someones help and they had no idea what I was talking about, and then he just bailed on me. I got it to work by using this as my first line of my IF statement in my macro

If Not Cell.Text = "0.00%" Or Cell.Value = False Then

Re: Differentiate Between True & False Cells

How would I setup up my code to do that?

I have a workbook with numerous spreadsheets. In these spreadsheets are numerous cells that contain IF formulas which are either true or false depending on the current date. I want to differentiate from the cells that are true and the cells that are false. if the cell goes to true it will return a value from an Add-in anywhere between 0-100. if the cell goes to false it will not call the Add-in and go to 0. The code I have so far goes cell by cell and tests it. The problem is that the way it tests the cell is by checking if the cell is equal to -1 for true and equal to 0 for false. Sometimes the cell IF formulas will be true and also have a value of 0, and I can never get -1 value in a cell. Also I cannot use a value other than 0 for False because all of the cells whether they are true or false are used by other cells which produce totals. Therefore I cannot use a space or a - as the value of the cell when it is false. Here is the code that I use to check to see if the cell is true or not.

'MX-2
    Dim Cell As Range
    
    Sheets("MX-2").Select
           
    For Each Cell In Range("B8:H39")
        If Cell.Value = True Then
            'code
        End If
    Next Cell

I have also attached a example of what i have.

Re: Alter Each Cell Individually True/False

Dont ask me to attach something and then tell me its too late for my attachment. Also it doesnt matter what the add-in is. All you have to know is that it returns a value between 0-100 which i have mentioned numerous times.

Re: Alter Each Cell Individually True/False

if the IF statement in the cell is true it will enact a add-in which retrieves a value which can be anywhere from 0 to 100. the IF statement in the cell whne false produces a 0. The IF statement in each cell is related to the date. if the date next to the cells is before or equal to NOW() then it goes to true, if not then it goes to false. the column in pink is related to each of the cells to its left. apparently if there is anything but a number in the cell it will screw up the cells that depend on the cell. i dont know if this will help because you dont have the add-ins that are required for the formulas to properly work.

Re: Alter Each Cell Individually True/False

I cant return a value other than zero because the numbers are used in monthly totals and if i use a "-" it will give me a #VALUE!. I cant use like "-1" because it will throw off the totals.

=IF(A38<=NOW(),retrieves a value from a add-in,0)

all of the If statements in the cells are basically the same. I suppose I could use another column as you say, but the If statement in my code that tests each cell wont differentiate from a cell that is true, that could return a 0 value, and a cell that is false which defaults to 0. If ya could tell me something to put in for the false default that wont screw up my totals that would work fine. I tried using a dash, "-", and space," ".

Re: Alter Each Cell Individually True/False

Okay so all the cells have an IF formula in them. if they cell is false it goes go 0. if they cell is true then i could go to any number from 0 to 100. i need all of the cells that are true to be altered, in that the value of them is copied over the formula. I need all the cells that are false to be left alone. the problem with what you, norie, suggested is that the cells sometimes return a 0 even when true. so i need to differentiate between the cells that are true and the ones that are false by not using the value of the cell. Also the cells that are false which return a 0 need to stay that way. I realize this is pain but there must be some kind of way to do this.
thanks

Re: Alter Each Cell Individually True/False

By removing the =True this only altered the cells that have values in them other than zero. I need the cells altered that have zeros and non zeros in them. I need the cells with the IF formulas that are in the true sense to be set apart from the false ones. here is the code that i have. It is a real simple copy and pastespecial values.

Dim Cell As Range
    
    Sheets("MX-2").Select
    ActiveSheet.Unprotect
        
    For Each Cell In Range("B8:M39")
        If Cell.Value = True Then
            Cell.Copy
            Cell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
            Application.CutCopyMode = False
        End If
    Next Cell

Re: Alter Each Cell Individually True/False

I have problem with this code. It works in that it goes cell by cell in the desginated range but it is evaluating the cell's value. -1 for true and 0 for false. Not if the IF formula is true or false. so the only way the cell would be altered is if there is a -1 or 0 for the value in the cell. I want it so that if the IF formula is true or false then it is altered, not if the cell is -1 or 0. hmm

Re: Alter Each Cell Individually True/False

Thanks that helped. I am still learning the syntax of VBA.

I have a large workbook that has about 8 spreadsheets. Each sheet has a large amount of cells that contain IF formulas. I will have a specified range and in that range i want to go cell by cell and alter each cell that is true/false. How would I go about doing this? thanks to anyone who can give me some help or point me in the right direction.

Re: Run Macro from cmd button error

that one line of code did it for me. thanks a lot. this has been drivin' me nuts.

Re: Run Macro from cmd button error

no, I do not want it to open at startup. Lots of people use it for read-only purposes. I tried just putting the code in the command button. no such luck tho. I tried your code royUK. It did the same thing. It has to be something within the setting or the controls of excel with respect to the macros. Its very odd because if I run the macro from the list it works, but not with the command button. What is also odd is that I have other command buttons that run macros and they work fine. I dont know what to do. If I cant get it to work then I cant get it to work. What can ya do.

Re: Run Macro from cmd button error

i tried this on my computer at home and it does work. it must be something with the settings at my work computer.

Re: Run Macro from cmd button error

This is the code for Module1

Sub ShowPass()
    UserForm1.Show
End Sub

UserForm1 does exist. This is the code in UserForm1

Private Sub cmdOK_Click()
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        If wSheet.ProtectContents = True Then
            wSheet.Unprotect Password:=TextBox1.Text
        Else
            wSheet.Protect Password:=TextBox1.Text
        End If
    Next wSheet
    Unload Me
End Sub


Private Sub UserForm_Click()


End Sub

This is the code for the command button on the the spreadsheet

Private Sub cmdPass_Click()
   Call ShowPass
End Sub

Are there some sort of rules that I could have in place that I dont know about. I just got this computer from somebody else at work recently and havent yet become too familiar with it. I've tried this on my neighbors computer with no such luck. thanks for the help guys

I am having trouble running a macro that I got off this site. It just protect/unprotects all the sheets at once. I want to just have a cmd button that runs it instead of have to go up to the macrolist. People will be using this that have no idea what a macro is. I get an error that reads "Run-time error '1004': Method 'Protect' of object '_Worksheet' failed". If I just run the macro from the list, no error. I have the cmd button call the module and then the module shows the userform. When I debug it takes me to either of the lines within the if else statement, depending on if the sheet is already protected or not. Here is my code, and any help would be much appreciated.

Private Sub cmdPass_Click()
   Call ShowPass
End Sub


Sub ShowPass()
    UserForm1.Show
End Sub


Private Sub cmdOK_Click()
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        If wSheet.ProtectContents = True Then
            wSheet.Unprotect Password:=TextBox1.Text
        Else
            wSheet.Protect Password:=TextBox1.Text
        End If
    Next wSheet
    Unload Me
End Sub


Private Sub UserForm_Click()


End Sub

thanks