First, a posting tip: Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Thanks!Second, before we address your time stamp issue, I would like to offer a few comments on the Command Button code. 1Read more
First, a posting tip:
Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Thanks!
Second, before we address your time stamp issue, I would like to offer a few comments on the Command Button code.
1 – You have a Dim statement for a variable named emptyRow, yet you never use the variable in the code. I don’t see the need for the Dim statement.
2 – There is no need to Select the ActiveCell. VBA can work directly on the ActiveCell.
3 – You are Protecting the sheet with the code, but you are not setting a Password. That means that any user could simply Unprotect the sheet and do whatever they like, not only to the sheet but also to the code itself.
If I were to use a CommandButton for this task (which I probably wouldn’t) I might do it like this:
A – Select the entire Sheet and Unlock all Cells B – Select and Lock any specific cells where you do not want to allow manual entry, e.g. A1:A10 C – Protect the Sheet with a Password.
Once that is done, the users can still enter data in the Unlocked cells, but they won’t be able to enter data the Locked cells, e.g. A1:A10. They also won’t be able to Unprotect the sheet since it is Password protected.
You could then use the following code, but there is one more key point to be aware of:
Unless you Protect the code and Hide it within the VBA editor, the users will be able to see the Password and also alter the code. You must set the VBAProject Properties to “Lock for viewing” and apply a Password to the code or you are defeating the entire purpose of protecting the sheet.
As I said earlier, if I were to use a CommandButton for this task, my code would probably look something like this:
Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="myPassword"
ActiveCell.Value = Time
ActiveSheet.Protect Password:="myPassword"
End Sub
With all that said, you don’t need to use a Command Button just to insert a Time Stamp.
If you want to restrict users from manually entering data in a specific set of cells and automatically enter a time stamp into a selected cell, you could do it with the Selection_Change event.
A – Select the entire Sheet and Unlock all Cells B – Select and Lock any specific cells where you want the timestamp(s), e.g. A1:A10 C – Protect the Sheet with a Password. D – Right Click the Sheet tab for the sheet where you want these time stamps and paste in the following code.
When the user clicks in any single cell within the Range A1:A10, the current time will be placed in the cell, but no other entries will be allowed. If they click in any other cell, manual entry will be permitted since those cells are Unlocked.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Ensure only one cell within A1:A10 is Selected
If Selection.CountLarge = 1 Then
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
'Insert time stamp in Selected cell
ActiveSheet.Unprotect Password:="myPassword"
Target = Time
ActiveSheet.Protect Password:="myPassword"
End If
End If
End Sub
If you want to further restrict user action once a time stamp has been set for a given cell, you can use something like this which will prevent the user from changing the time stamp once the code has set it.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Ensure only one cell within A1:A10 is Selected
If Selection.CountLarge = 1 Then
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
'If cell contains data, do not allow changes
If Target <> "" Then
MsgBox "Time Stamp Already Set" & vbCrLf & vbCrLf & _
"User Changes Not Allowed"
Exit Sub
End If
'If cell is empty, Insert time stamp
ActiveSheet.Unprotect Password:="myPassword"
Target = Time
ActiveSheet.Protect Password:="myPassword"
End If
End If
End Sub
Another option to consider is to let the VBA code enter the time stamp in Column A once some other cell has had data entered into it. That would eliminate any need for the user to select a time stamp cell. For example, you could use the following code to insert a time stamp in Column A in the same Row as data was entered into B1:F10.
Private Sub Worksheet_Change(ByVal Target As Range)
'Ensure only one cell within A1:A10 is Selected
If Selection.CountLarge = 1 Then
If Not Intersect(Target, Range("B1:F10")) Is Nothing Then
'Insert time stamp in Column A of Target Row
ActiveSheet.Unprotect Password:="myPassword"
Range("A" & Target.Row) = Time
ActiveSheet.Protect Password:="myPassword"
End If
End If
End Sub
As a reminder, you must Hide and Protect the VBA code or the users will be able to Unprotect the sheet as well as alter the code.
You can't "turn off" the update by setting anything equal to "False".What you need to do is either:1 - Use VBA to replace the formula with the current value, thus losing the formula. In it's simplest form it would look like this:Sub MakeStatic() Range("A1") = Range("A1").Value End Sub Based on yourRead more
You can’t “turn off” the update by setting anything equal to “False”.
What you need to do is either:
1 – Use VBA to replace the formula with the current value, thus losing the formula. In it’s simplest form it would look like this:
Sub MakeStatic()
Range("A1") = Range("A1").Value
End Sub
Based on your example, something like this should work:
Sub MakeStaticRng()
For rw = 3 To 3000
If Range("B" & rw) < Range("F1") Then
Range("C" & rw) = Range("C" & rw).Value
End If
Next
End Sub
Note: I suggest you test this in a backup copy of your workbook, since you are going to lose the formulas and Macros cannot be undone.
If you don’t want to lose the formula, then you would need VBA to put the formula back in the cell based on some other criteria.
Bottom line is that you can’t have a formula that’s dependent on a volatile function like TODAY() and a static value in a cell at the same time.
If you use this code in a Worksheet_Change event, it will fire automatically when your worksheet changes. You can control when it does what it is does by having the code test the address of the changed cell so that it doesn’t fire unnecessarily. If that is of interest to you, you’ll need to tell us what change(s) should trigger the code.
Or…
2 – Eliminate the formula completely and use VBA to do the entire calculation and place the results in the cells. That’s a bit more complicated, but it could be done.
Here is another way.Put your Data in Columns A - D - GWe are going to use Columns B & C to check Column D & GColumns E & F to check Column A & GColumns H & I to check Columns A & DIn Column B enter the formula: =IF(ISERROR(MATCH(A2,$D$2:$D$13,0)),"No Match in D","")Were matchRead more
Here is another way. Put your Data in Columns A – D – G
We are going to use Columns B & C to check Column D & G Columns E & F to check Column A & G Columns H & I to check Columns A & D
In Column B enter the formula: =IF(ISERROR(MATCH(A2,$D$2:$D$13,0)),”No Match in D”,””) Were matching column A to D
In Column C enter the formula: =IF(ISERROR(MATCH(A2,$G$2:$G$13,0)),”No Match in G”,””) Were matching column A to G
In Column E enter the formula: =IF(ISERROR(MATCH(D2,$A$2:$A$13,0)),”No Match in A”,””) Were matching column D to A
In Column F enter the formula: =IF(ISERROR(MATCH(D2,$G$2:$G$13,0)),”No Match in G”,””) Were matching column D to G
I’ll leave the last two columns H & I for you to do.
I tried posting an example, but the pre tags would not work.
pinging a wireless connected device then you will start getting the High Respond Time Wireless has inherent issues with lag and latency so you can expect slower responses when accessing a wifi network from a LAN and vice versa. But, 1000 ms ping response times are way higher than I would expect.I'mRead more
pinging a wireless connected device then you will start getting the High Respond Time
Wireless has inherent issues with lag and latency so you can expect slower responses when accessing a wifi network from a LAN and vice versa. But, 1000 ms ping response times are way higher than I would expect.
I’m not at all familiar with your wireless devices but that’s where I would start looking for issues if it were me.
The first question that comes to my mind is, how is performance when going from one wifi client to another? Are the ping responses as slow as when you go from the wired network to the wireless? How about when you go from wifi to wired? Run a few ping tests and get back to us with the results.
It matters not how straight the gate, How charged with punishments the scroll, I am the master of my fate; I am the captain of my soul.
Your DNS is being provided to you by your ISP. You are probably not going to be able to ping it but that does not mean it is not working. To test your DNS you need to ping something by its FQDN like...PING YAHOO.COMIt you should resolve and IP for YAHOO and start pinging it. Try the command above anRead more
Your DNS is being provided to you by your ISP. You are probably not going to be able to ping it but that does not mean it is not working. To test your DNS you need to ping something by its FQDN like…
PING YAHOO.COM
It you should resolve and IP for YAHOO and start pinging it. Try the command above and tell us what you get.
You could also try using a
IPCONFIG /FLUSHDNS IPCONFIG /REGISTERDNS
This will flush your local DNS cache and reload. Then do…
PING YAHOO.COM IPCONFIG /PRINTDNS
And you should see yahoo loaded in your local DNS.
That is OtheHill. I suggested using those three keys because that works on a Compaq laptop I have in front of me now. If in fact depressing those three keys together caused some change in keyboard behavior, then using the same key strokes again should toggle you back to the original state.I suggestRead more
That is OtheHill. I suggested using those three keys because that works on a Compaq laptop I have in front of me now.
If in fact depressing those three keys together caused some change in keyboard behavior, then using the same key strokes again should toggle you back to the original state.
I suggest you download the manual for that model. Read any propitiatory key stroke actions for that model.
The first link below is general. The second deals with the keyboard and touchpad. I also suggest trying an external keyboard to see if it is a defective keyboard.
If you are going to charge for your services you need to learn how to research this stuff yourself.
A floppy drive is not IDE but that's just an aside.For multiple entries in device manager the first thing to try is to delete both entries and then on reboot let windows set them up again. Another option, if you have no IDE drives connected, is to disable that controller in bios setup.You apparentlyRead more
A floppy drive is not IDE but that’s just an aside.
For multiple entries in device manager the first thing to try is to delete both entries and then on reboot let windows set them up again. Another option, if you have no IDE drives connected, is to disable that controller in bios setup.
You apparently want to have three SATA drives. That shouldn’t be a problem as long as you have three SATA motherboard connectors. If you don’t then you’ll have to add a SATA PCI card or have the third drive connected externally via USB. Are you sure the other Dells have three SATA drives or could at least one of the drives be IDE?
You mention what regedit, explorer and disk management see but what really matters is what the bios sees. If the bios sees a drive then windows will see it too.
After the CMOS is reset, you will have to immediately access the BIOS to correct the date/time & numerous other settings.Here's a suggestion - shutdown the system, then temporarily disconnect the HDD & remove the USB card. Connect the keyboard to one of the rear USB ports, do NOT connect a mRead more
After the CMOS is reset, you will have to immediately access the BIOS to correct the date/time & numerous other settings.
Here’s a suggestion – shutdown the system, then temporarily disconnect the HDD & remove the USB card. Connect the keyboard to one of the rear USB ports, do NOT connect a mouse or any other USB devices. Boot the system & immediately begin tapping the BIOS access key (Del, F1, F2, etc) & see what happens.
If it doesn’t get you in, shutdown & reconnect everything as it was. Then, rather than reinstalling Windows, try a good old fashioned manual cleanup – delete, uninstall, run a cleaner, MSCONFIG, scan for viruses/malware, defrag, etc. In other words, all the things you should be doing regularly all along.
Did you make changes and can't 'undo' them?Turn off sticky keys:https://www.isunshare.com/windows-1...Registry restore:https://www.windowscentral.com/how-...Or maybe it's a hardware problem? Try another keyboard or an external one if it's a laptop. Otherwise let us know of anything that happened thaRead more
Or maybe it’s a hardware problem? Try another keyboard or an external one if it’s a laptop. Otherwise let us know of anything that happened that may have led up to the problem.
Word Doc Opens All Jumbled
Try here:http://support.microsoft.com/kb/316951MIKEhttp://www.skeptic.com/
http://support.microsoft.com/kb/316951
MIKE
http://www.skeptic.com/
Restrict MS Excel Cell Input By Command Button Only
First, a posting tip: Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Thanks!Second, before we address your time stamp issue, I would like to offer a few comments on the Command Button code. 1Read more
Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Thanks!
Second, before we address your time stamp issue, I would like to offer a few comments on the Command Button code.
1 – You have a Dim statement for a variable named emptyRow, yet you never use the variable in the code. I don’t see the need for the Dim statement.
2 – There is no need to Select the ActiveCell. VBA can work directly on the ActiveCell.
3 – You are Protecting the sheet with the code, but you are not setting a Password. That means that any user could simply Unprotect the sheet and do whatever they like, not only to the sheet but also to the code itself.
If I were to use a CommandButton for this task (which I probably wouldn’t) I might do it like this:
A – Select the entire Sheet and Unlock all Cells
B – Select and Lock any specific cells where you do not want to allow manual entry, e.g. A1:A10
C – Protect the Sheet with a Password.
Once that is done, the users can still enter data in the Unlocked cells, but they won’t be able to enter data the Locked cells, e.g. A1:A10. They also won’t be able to Unprotect the sheet since it is Password protected.
You could then use the following code, but there is one more key point to be aware of:
Unless you Protect the code and Hide it within the VBA editor, the users will be able to see the Password and also alter the code. You must set the VBAProject Properties to “Lock for viewing” and apply a Password to the code or you are defeating the entire purpose of protecting the sheet.
As I said earlier, if I were to use a CommandButton for this task, my code would probably look something like this:
Sub CommandButton1_Click() ActiveSheet.Unprotect Password:="myPassword" ActiveCell.Value = Time ActiveSheet.Protect Password:="myPassword" End SubWith all that said, you don’t need to use a Command Button just to insert a Time Stamp.
If you want to restrict users from manually entering data in a specific set of cells and automatically enter a time stamp into a selected cell, you could do it with the Selection_Change event.
A – Select the entire Sheet and Unlock all Cells
B – Select and Lock any specific cells where you want the timestamp(s), e.g. A1:A10
C – Protect the Sheet with a Password.
D – Right Click the Sheet tab for the sheet where you want these time stamps and paste in the following code.
When the user clicks in any single cell within the Range A1:A10, the current time will be placed in the cell, but no other entries will be allowed. If they click in any other cell, manual entry will be permitted since those cells are Unlocked.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Ensure only one cell within A1:A10 is Selected If Selection.CountLarge = 1 Then If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 'Insert time stamp in Selected cell ActiveSheet.Unprotect Password:="myPassword" Target = Time ActiveSheet.Protect Password:="myPassword" End If End If End SubIf you want to further restrict user action once a time stamp has been set for a given cell, you can use something like this which will prevent the user from changing the time stamp once the code has set it.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Ensure only one cell within A1:A10 is Selected If Selection.CountLarge = 1 Then If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 'If cell contains data, do not allow changes If Target <> "" Then MsgBox "Time Stamp Already Set" & vbCrLf & vbCrLf & _ "User Changes Not Allowed" Exit Sub End If 'If cell is empty, Insert time stamp ActiveSheet.Unprotect Password:="myPassword" Target = Time ActiveSheet.Protect Password:="myPassword" End If End If End SubAnother option to consider is to let the VBA code enter the time stamp in Column A once some other cell has had data entered into it. That would eliminate any need for the user to select a time stamp cell. For example, you could use the following code to insert a time stamp in Column A in the same Row as data was entered into B1:F10.
Private Sub Worksheet_Change(ByVal Target As Range) 'Ensure only one cell within A1:A10 is Selected If Selection.CountLarge = 1 Then If Not Intersect(Target, Range("B1:F10")) Is Nothing Then 'Insert time stamp in Column A of Target Row ActiveSheet.Unprotect Password:="myPassword" Range("A" & Target.Row) = Time ActiveSheet.Protect Password:="myPassword" End If End If End SubAs a reminder, you must Hide and Protect the VBA code or the users will be able to Unprotect the sheet as well as alter the code.
Click Here Before Posting Data or VBA Code —> How To Post Data or Code.
How Do Store Calculated Value As Static Entry
You can't "turn off" the update by setting anything equal to "False".What you need to do is either:1 - Use VBA to replace the formula with the current value, thus losing the formula. In it's simplest form it would look like this:Sub MakeStatic() Range("A1") = Range("A1").Value End Sub Based on yourRead more
What you need to do is either:
1 – Use VBA to replace the formula with the current value, thus losing the formula. In it’s simplest form it would look like this:
Sub MakeStatic() Range("A1") = Range("A1").Value End SubBased on your example, something like this should work:
Sub MakeStaticRng() For rw = 3 To 3000 If Range("B" & rw) < Range("F1") Then Range("C" & rw) = Range("C" & rw).Value End If Next End SubNote: I suggest you test this in a backup copy of your workbook, since you are going to lose the formulas and Macros cannot be undone.
If you don’t want to lose the formula, then you would need VBA to put the formula back in the cell based on some other criteria.
Bottom line is that you can’t have a formula that’s dependent on a volatile function like TODAY() and a static value in a cell at the same time.
If you use this code in a Worksheet_Change event, it will fire automatically when your worksheet changes. You can control when it does what it is does by having the code test the address of the changed cell so that it doesn’t fire unnecessarily. If that is of interest to you, you’ll need to tell us what change(s) should trigger the code.
Or…
2 – Eliminate the formula completely and use VBA to do the entire calculation and place the results in the cells. That’s a bit more complicated, but it could be done.
Compare 3 Columns
Here is another way.Put your Data in Columns A - D - GWe are going to use Columns B & C to check Column D & GColumns E & F to check Column A & GColumns H & I to check Columns A & DIn Column B enter the formula: =IF(ISERROR(MATCH(A2,$D$2:$D$13,0)),"No Match in D","")Were matchRead more
Put your Data in Columns A – D – G
We are going to use
Columns B & C to check Column D & G
Columns E & F to check Column A & G
Columns H & I to check Columns A & D
In Column B enter the formula:
=IF(ISERROR(MATCH(A2,$D$2:$D$13,0)),”No Match in D”,””)
Were matching column A to D
In Column C enter the formula:
=IF(ISERROR(MATCH(A2,$G$2:$G$13,0)),”No Match in G”,””)
Were matching column A to G
In Column E enter the formula:
=IF(ISERROR(MATCH(D2,$A$2:$A$13,0)),”No Match in A”,””)
Were matching column D to A
In Column F enter the formula:
=IF(ISERROR(MATCH(D2,$G$2:$G$13,0)),”No Match in G”,””)
Were matching column D to G
I’ll leave the last two columns H & I for you to do.
I tried posting an example, but the pre tags would not work.
MIKE
http://www.skeptic.com/
How To Reduce Time-To-Live (TTL) Setting On Wireless Network
pinging a wireless connected device then you will start getting the High Respond Time Wireless has inherent issues with lag and latency so you can expect slower responses when accessing a wifi network from a LAN and vice versa. But, 1000 ms ping response times are way higher than I would expect.I'mRead more
Wireless has inherent issues with lag and latency so you can expect slower responses when accessing a wifi network from a LAN and vice versa. But, 1000 ms ping response times are way higher than I would expect.
I’m not at all familiar with your wireless devices but that’s where I would start looking for issues if it were me.
The first question that comes to my mind is, how is performance when going from one wifi client to another? Are the ping responses as slow as when you go from the wired network to the wireless? How about when you go from wifi to wired? Run a few ping tests and get back to us with the results.
It matters not how straight the gate,
How charged with punishments the scroll,
I am the master of my fate;
I am the captain of my soul.
***William Henley***
Cannot Ping DNS Server On My PC
Your DNS is being provided to you by your ISP. You are probably not going to be able to ping it but that does not mean it is not working. To test your DNS you need to ping something by its FQDN like...PING YAHOO.COMIt you should resolve and IP for YAHOO and start pinging it. Try the command above anRead more
PING YAHOO.COM
It you should resolve and IP for YAHOO and start pinging it. Try the command above and tell us what you get.
You could also try using a
IPCONFIG /FLUSHDNS
IPCONFIG /REGISTERDNS
This will flush your local DNS cache and reload. Then do…
PING YAHOO.COM
IPCONFIG /PRINTDNS
And you should see yahoo loaded in your local DNS.
The HP 550 Laptop Lost The Option Of BIOS, There Is No Menu
That is OtheHill. I suggested using those three keys because that works on a Compaq laptop I have in front of me now. If in fact depressing those three keys together caused some change in keyboard behavior, then using the same key strokes again should toggle you back to the original state.I suggestRead more
If in fact depressing those three keys together caused some change in keyboard behavior, then using the same key strokes again should toggle you back to the original state.
I suggest you download the manual for that model. Read any propitiatory key stroke actions for that model.
The first link below is general. The second deals with the keyboard and touchpad. I also suggest trying an external keyboard to see if it is a defective keyboard.
If you are going to charge for your services you need to learn how to research this stuff yourself.
http://h20000.www2.hp.com/bizsuppor…
http://bizsupport1.austin.hp.com/bc…
Solved Switching From Dual Channel IDE To Standard SATA AHCI
A floppy drive is not IDE but that's just an aside.For multiple entries in device manager the first thing to try is to delete both entries and then on reboot let windows set them up again. Another option, if you have no IDE drives connected, is to disable that controller in bios setup.You apparentlyRead more
For multiple entries in device manager the first thing to try is to delete both entries and then on reboot let windows set them up again. Another option, if you have no IDE drives connected, is to disable that controller in bios setup.
You apparently want to have three SATA drives. That shouldn’t be a problem as long as you have three SATA motherboard connectors. If you don’t then you’ll have to add a SATA PCI card or have the third drive connected externally via USB. Are you sure the other Dells have three SATA drives or could at least one of the drives be IDE?
You mention what regedit, explorer and disk management see but what really matters is what the bios sees. If the bios sees a drive then windows will see it too.
Solved Onboard USB Ports Not Working
After the CMOS is reset, you will have to immediately access the BIOS to correct the date/time & numerous other settings.Here's a suggestion - shutdown the system, then temporarily disconnect the HDD & remove the USB card. Connect the keyboard to one of the rear USB ports, do NOT connect a mRead more
Here’s a suggestion – shutdown the system, then temporarily disconnect the HDD & remove the USB card. Connect the keyboard to one of the rear USB ports, do NOT connect a mouse or any other USB devices. Boot the system & immediately begin tapping the BIOS access key (Del, F1, F2, etc) & see what happens.
If it doesn’t get you in, shutdown & reconnect everything as it was. Then, rather than reinstalling Windows, try a good old fashioned manual cleanup – delete, uninstall, run a cleaner, MSCONFIG, scan for viruses/malware, defrag, etc. In other words, all the things you should be doing regularly all along.
Solved Keyboard Won’t Type Unless I Hold Down Shift
Did you make changes and can't 'undo' them?Turn off sticky keys:https://www.isunshare.com/windows-1...Registry restore:https://www.windowscentral.com/how-...Or maybe it's a hardware problem? Try another keyboard or an external one if it's a laptop. Otherwise let us know of anything that happened thaRead more
Turn off sticky keys:
https://www.isunshare.com/windows-1…
Registry restore:
https://www.windowscentral.com/how-…
Or maybe it’s a hardware problem? Try another keyboard or an external one if it’s a laptop. Otherwise let us know of anything that happened that may have led up to the problem.