| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thin Borders
Hi I have this code working perfectly (thanks to a lot of help from people on
here) I have it so thick borders go around each question 1, 2 and 3 but I need thin ones to go in between the sub questions e.g 3a, 3b, 3c - can anyone help? Here is the code. Sub Questionaire() Dim Msg1, Msg2, Msg3, Msg4, Msg5 Msg1 = "Question 1. Do you store any potentially sensitive information(e.g customer details, account balance information or any memorable data relating to any customers) on a portable device?" Msg2 = "Question 2. Do you have any commercial need to store information on your C-drive?" Msg3 = "Question 3. Have you ever known of an incident in your area where a portable device has been lost or stolen?" Msg4 = "Question 3c. Could this information have been detrimental to Business Operations or Customers if it fell into the wrong hands?" Application.ScreenUpdating = False storeSI = MsgBox(Msg1, vbYesNo + vbQuestion) If storeSI = vbYes Then Range("D2") = Msg1 Range("E2") = "Yes" Columns("E").AutoFit Range("D2:E4").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium sortSI = InputBox("Question 1b. What sort of information is held?") Range("D3") = "Question 1b. What sort of information is held?" Range("E3") = sortSI Range("E3").Font.ColorIndex = 5 portDev = InputBox("Question 1c. Please state whether the information is held on a PDA or laptop") Range("D4") = "Question 1c. Please state whether the information is held on a PDA or laptop" Range("E4") = portDev Range("E4").Font.ColorIndex = 5 Else Range("D2") = Msg1 Range("E2") = "No" Range("D2:E2").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If storeCdrv = MsgBox(Msg2, vbYesNo + vbQuestion) If storeCdrv = vbYes Then Range("D6") = Msg2 Range("E6") = "Yes" Range("E7") = storeCdrv Columns("E").AutoFit Range("D6:E7").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium HrdDrv = InputBox("Question 2a. Please state what the commercial need is") Range("D7") = "Question 2a. Please state what the commercial need is" Range("E7") = HrdDrv Range("E7").Font.ColorIndex = 5 Else Range("D6") = Msg2 Range("E6") = "No" Range("D6:E6").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If portThft = MsgBox(Msg3, vbYesNo + vbQuestion) If portThft = vbYes Then Range("D9") = Msg3 Range("E9") = "Yes" Range("E9").Font.ColorIndex = 5 heldC = MsgBox("Question 3a. Was there any information held on the C-drive at the time?", vbYesNo + vbQuestion) Else Range("D9") = Msg3 Range("E9") = "No" Range("E9").Font.ColorIndex = 5 Range("D9:E9").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If If heldC = vbYes Then Range("D10") = "Question 3a. Was there any information held on the C-drive at the time?" Range("E10") = "Yes" Range("E10").Font.ColorIndex = 5 Else Range("D9") = Msg3 Range("E9") = "Yes" Range("D10") = "Question 3a. Was there any information held on the C-drive at the time?" Range("E10") = "No" Range("E9").Font.ColorIndex = 5 Range("E10").Font.ColorIndex = 5 Range("D9:E10").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If If heldC = vbYes Then whatC = InputBox("Question 3b. What information was held on the C-drive?") Range("D11") = "Question 3b. What information was held on the C-drive?" Range("E11") = whatC Range("E11").Font.ColorIndex = 5 detri = MsgBox(Msg4, vbYesNo + vbQuestion) If detri = vbYes Then Range("D12") = Msg4 Range("E12") = "Yes" Range("E12").Font.ColorIndex = 5 WhyDetri = InputBox("Question 3d. Why?") Range("D13") = "Question 3d. Why?" Range("E13") = WhyDetri Range("E13").Font.ColorIndex = 5 Columns("E").AutoFit Range("D9:E13").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Else Range("D12") = Msg4 Range("E12") = "No" Range("E12").Font.ColorIndex = 5 Columns("E").AutoFit Range("D9:E12").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If End If Application.ScreenUpdating = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Thin Borders
Change the Weight:=XLMedium to Weight:=XLThin
Corey.... "Pasty" wrote in message ... Hi I have this code working perfectly (thanks to a lot of help from people on here) I have it so thick borders go around each question 1, 2 and 3 but I need thin ones to go in between the sub questions e.g 3a, 3b, 3c - can anyone help? Here is the code. Sub Questionaire() Dim Msg1, Msg2, Msg3, Msg4, Msg5 Msg1 = "Question 1. Do you store any potentially sensitive information(e.g customer details, account balance information or any memorable data relating to any customers) on a portable device?" Msg2 = "Question 2. Do you have any commercial need to store information on your C-drive?" Msg3 = "Question 3. Have you ever known of an incident in your area where a portable device has been lost or stolen?" Msg4 = "Question 3c. Could this information have been detrimental to Business Operations or Customers if it fell into the wrong hands?" Application.ScreenUpdating = False storeSI = MsgBox(Msg1, vbYesNo + vbQuestion) If storeSI = vbYes Then Range("D2") = Msg1 Range("E2") = "Yes" Columns("E").AutoFit Range("D2:E4").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium sortSI = InputBox("Question 1b. What sort of information is held?") Range("D3") = "Question 1b. What sort of information is held?" Range("E3") = sortSI Range("E3").Font.ColorIndex = 5 portDev = InputBox("Question 1c. Please state whether the information is held on a PDA or laptop") Range("D4") = "Question 1c. Please state whether the information is held on a PDA or laptop" Range("E4") = portDev Range("E4").Font.ColorIndex = 5 Else Range("D2") = Msg1 Range("E2") = "No" Range("D2:E2").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If storeCdrv = MsgBox(Msg2, vbYesNo + vbQuestion) If storeCdrv = vbYes Then Range("D6") = Msg2 Range("E6") = "Yes" Range("E7") = storeCdrv Columns("E").AutoFit Range("D6:E7").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium HrdDrv = InputBox("Question 2a. Please state what the commercial need is") Range("D7") = "Question 2a. Please state what the commercial need is" Range("E7") = HrdDrv Range("E7").Font.ColorIndex = 5 Else Range("D6") = Msg2 Range("E6") = "No" Range("D6:E6").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If portThft = MsgBox(Msg3, vbYesNo + vbQuestion) If portThft = vbYes Then Range("D9") = Msg3 Range("E9") = "Yes" Range("E9").Font.ColorIndex = 5 heldC = MsgBox("Question 3a. Was there any information held on the C-drive at the time?", vbYesNo + vbQuestion) Else Range("D9") = Msg3 Range("E9") = "No" Range("E9").Font.ColorIndex = 5 Range("D9:E9").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If If heldC = vbYes Then Range("D10") = "Question 3a. Was there any information held on the C-drive at the time?" Range("E10") = "Yes" Range("E10").Font.ColorIndex = 5 Else Range("D9") = Msg3 Range("E9") = "Yes" Range("D10") = "Question 3a. Was there any information held on the C-drive at the time?" Range("E10") = "No" Range("E9").Font.ColorIndex = 5 Range("E10").Font.ColorIndex = 5 Range("D9:E10").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If If heldC = vbYes Then whatC = InputBox("Question 3b. What information was held on the C-drive?") Range("D11") = "Question 3b. What information was held on the C-drive?" Range("E11") = whatC Range("E11").Font.ColorIndex = 5 detri = MsgBox(Msg4, vbYesNo + vbQuestion) If detri = vbYes Then Range("D12") = Msg4 Range("E12") = "Yes" Range("E12").Font.ColorIndex = 5 WhyDetri = InputBox("Question 3d. Why?") Range("D13") = "Question 3d. Why?" Range("E13") = WhyDetri Range("E13").Font.ColorIndex = 5 Columns("E").AutoFit Range("D9:E13").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Else Range("D12") = Msg4 Range("E12") = "No" Range("E12").Font.ColorIndex = 5 Columns("E").AutoFit Range("D9:E12").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If End If Application.ScreenUpdating = True End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Thin Borders
I know this much its more the bit to make the lines inside the thick borders
go thin - I am a little unsure of the code to use - its gonna be a thick one outside all of them (which I have done) and then inside it thin borders splitting the sub questions up. Ta "Coza" wrote: Change the Weight:=XLMedium to Weight:=XLThin Corey.... "Pasty" wrote in message ... Hi I have this code working perfectly (thanks to a lot of help from people on here) I have it so thick borders go around each question 1, 2 and 3 but I need thin ones to go in between the sub questions e.g 3a, 3b, 3c - can anyone help? Here is the code. Sub Questionaire() Dim Msg1, Msg2, Msg3, Msg4, Msg5 Msg1 = "Question 1. Do you store any potentially sensitive information(e.g customer details, account balance information or any memorable data relating to any customers) on a portable device?" Msg2 = "Question 2. Do you have any commercial need to store information on your C-drive?" Msg3 = "Question 3. Have you ever known of an incident in your area where a portable device has been lost or stolen?" Msg4 = "Question 3c. Could this information have been detrimental to Business Operations or Customers if it fell into the wrong hands?" Application.ScreenUpdating = False storeSI = MsgBox(Msg1, vbYesNo + vbQuestion) If storeSI = vbYes Then Range("D2") = Msg1 Range("E2") = "Yes" Columns("E").AutoFit Range("D2:E4").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium sortSI = InputBox("Question 1b. What sort of information is held?") Range("D3") = "Question 1b. What sort of information is held?" Range("E3") = sortSI Range("E3").Font.ColorIndex = 5 portDev = InputBox("Question 1c. Please state whether the information is held on a PDA or laptop") Range("D4") = "Question 1c. Please state whether the information is held on a PDA or laptop" Range("E4") = portDev Range("E4").Font.ColorIndex = 5 Else Range("D2") = Msg1 Range("E2") = "No" Range("D2:E2").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If storeCdrv = MsgBox(Msg2, vbYesNo + vbQuestion) If storeCdrv = vbYes Then Range("D6") = Msg2 Range("E6") = "Yes" Range("E7") = storeCdrv Columns("E").AutoFit Range("D6:E7").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium HrdDrv = InputBox("Question 2a. Please state what the commercial need is") Range("D7") = "Question 2a. Please state what the commercial need is" Range("E7") = HrdDrv Range("E7").Font.ColorIndex = 5 Else Range("D6") = Msg2 Range("E6") = "No" Range("D6:E6").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If portThft = MsgBox(Msg3, vbYesNo + vbQuestion) If portThft = vbYes Then Range("D9") = Msg3 Range("E9") = "Yes" Range("E9").Font.ColorIndex = 5 heldC = MsgBox("Question 3a. Was there any information held on the C-drive at the time?", vbYesNo + vbQuestion) Else Range("D9") = Msg3 Range("E9") = "No" Range("E9").Font.ColorIndex = 5 Range("D9:E9").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If If heldC = vbYes Then Range("D10") = "Question 3a. Was there any information held on the C-drive at the time?" Range("E10") = "Yes" Range("E10").Font.ColorIndex = 5 Else Range("D9") = Msg3 Range("E9") = "Yes" Range("D10") = "Question 3a. Was there any information held on the C-drive at the time?" Range("E10") = "No" Range("E9").Font.ColorIndex = 5 Range("E10").Font.ColorIndex = 5 Range("D9:E10").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If If heldC = vbYes Then whatC = InputBox("Question 3b. What information was held on the C-drive?") Range("D11") = "Question 3b. What information was held on the C-drive?" Range("E11") = whatC Range("E11").Font.ColorIndex = 5 detri = MsgBox(Msg4, vbYesNo + vbQuestion) If detri = vbYes Then Range("D12") = Msg4 Range("E12") = "Yes" Range("E12").Font.ColorIndex = 5 WhyDetri = InputBox("Question 3d. Why?") Range("D13") = "Question 3d. Why?" Range("E13") = WhyDetri Range("E13").Font.ColorIndex = 5 Columns("E").AutoFit Range("D9:E13").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Else Range("D12") = Msg4 Range("E12") = "No" Range("E12").Font.ColorIndex = 5 Columns("E").AutoFit Range("D9:E12").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If End If Application.ScreenUpdating = True End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Thin Borders
Sub test()
DoBorders Range("B3:C8") End Sub Function DoBorders(rng As Range) On Error Resume Next With rng.Borders For i = 7 To 12 With .Item(i) .LineStyle = xlContinuous .Weight = IIf(i < 11, xlThick, xlThin) .ColorIndex = xlAutomatic End With Next End With End Function If only one column xlInsideVertical (11), or only one row xlInsideHorizontal (12) would fail without the error handling Regads, Peter T "Pasty" wrote in message ... I know this much its more the bit to make the lines inside the thick borders go thin - I am a little unsure of the code to use - its gonna be a thick one outside all of them (which I have done) and then inside it thin borders splitting the sub questions up. Ta "Coza" wrote: Change the Weight:=XLMedium to Weight:=XLThin Corey.... "Pasty" wrote in message ... Hi I have this code working perfectly (thanks to a lot of help from people on here) I have it so thick borders go around each question 1, 2 and 3 but I need thin ones to go in between the sub questions e.g 3a, 3b, 3c - can anyone help? Here is the code. Sub Questionaire() Dim Msg1, Msg2, Msg3, Msg4, Msg5 Msg1 = "Question 1. Do you store any potentially sensitive information(e.g customer details, account balance information or any memorable data relating to any customers) on a portable device?" Msg2 = "Question 2. Do you have any commercial need to store information on your C-drive?" Msg3 = "Question 3. Have you ever known of an incident in your area where a portable device has been lost or stolen?" Msg4 = "Question 3c. Could this information have been detrimental to Business Operations or Customers if it fell into the wrong hands?" Application.ScreenUpdating = False storeSI = MsgBox(Msg1, vbYesNo + vbQuestion) If storeSI = vbYes Then Range("D2") = Msg1 Range("E2") = "Yes" Columns("E").AutoFit Range("D2:E4").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium sortSI = InputBox("Question 1b. What sort of information is held?") Range("D3") = "Question 1b. What sort of information is held?" Range("E3") = sortSI Range("E3").Font.ColorIndex = 5 portDev = InputBox("Question 1c. Please state whether the information is held on a PDA or laptop") Range("D4") = "Question 1c. Please state whether the information is held on a PDA or laptop" Range("E4") = portDev Range("E4").Font.ColorIndex = 5 Else Range("D2") = Msg1 Range("E2") = "No" Range("D2:E2").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If storeCdrv = MsgBox(Msg2, vbYesNo + vbQuestion) If storeCdrv = vbYes Then Range("D6") = Msg2 Range("E6") = "Yes" Range("E7") = storeCdrv Columns("E").AutoFit Range("D6:E7").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium HrdDrv = InputBox("Question 2a. Please state what the commercial need is") Range("D7") = "Question 2a. Please state what the commercial need is" Range("E7") = HrdDrv Range("E7").Font.ColorIndex = 5 Else Range("D6") = Msg2 Range("E6") = "No" Range("D6:E6").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If portThft = MsgBox(Msg3, vbYesNo + vbQuestion) If portThft = vbYes Then Range("D9") = Msg3 Range("E9") = "Yes" Range("E9").Font.ColorIndex = 5 heldC = MsgBox("Question 3a. Was there any information held on the C-drive at the time?", vbYesNo + vbQuestion) Else Range("D9") = Msg3 Range("E9") = "No" Range("E9").Font.ColorIndex = 5 Range("D9:E9").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If If heldC = vbYes Then Range("D10") = "Question 3a. Was there any information held on the C-drive at the time?" Range("E10") = "Yes" Range("E10").Font.ColorIndex = 5 Else Range("D9") = Msg3 Range("E9") = "Yes" Range("D10") = "Question 3a. Was there any information held on the C-drive at the time?" Range("E10") = "No" Range("E9").Font.ColorIndex = 5 Range("E10").Font.ColorIndex = 5 Range("D9:E10").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If If heldC = vbYes Then whatC = InputBox("Question 3b. What information was held on the C-drive?") Range("D11") = "Question 3b. What information was held on the C-drive?" Range("E11") = whatC Range("E11").Font.ColorIndex = 5 detri = MsgBox(Msg4, vbYesNo + vbQuestion) If detri = vbYes Then Range("D12") = Msg4 Range("E12") = "Yes" Range("E12").Font.ColorIndex = 5 WhyDetri = InputBox("Question 3d. Why?") Range("D13") = "Question 3d. Why?" Range("E13") = WhyDetri Range("E13").Font.ColorIndex = 5 Columns("E").AutoFit Range("D9:E13").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Else Range("D12") = Msg4 Range("E12") = "No" Range("E12").Font.ColorIndex = 5 Columns("E").AutoFit Range("D9:E12").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium End If End If Application.ScreenUpdating = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Button, now too thin, cannot select to delete | Excel Worksheet Functions | |||
| Pie chart slices too thin | Excel Programming | |||
| thin # of samples by averaging | Excel Worksheet Functions | |||
| How to create thin cell border | Excel Discussion (Misc queries) | |||
| Control Toolbox appears out of thin air | Excel Programming | |||
Linear Mode
