I'm looking to set up a spreadsheet whereby individuals answer questions and have to format their answer using a particular font, colour, font size and so on. The idea is that I can then compare their answer sheet to a pre completed one using an =IF function and get a total score. The only problem is =if and =exact only lookup cell text/numbers and don't look at how the text is formatted within them.
I've created a combo box in excel with the macro recorder. This is the code that came back:
Sub test1() ActiveSheet.DropDowns.Add(1305, 52.5, 242.25, 39).Select With Selection .ListFillRange = "$AW$4:$AW$18" .LinkedCell = "$AX$5" .DropDownLines = 5 .Display3DShading = False End With End Sub
This always results in a default font size of 8 for my list. Can anyone tell me how to modify the code above to change the font size to 14? And how to modify the color of the list.
I use this ok code to increase the font in the selected cell:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim TargetRange As Range Dim isect Set TargetRange = Range("C:D") Set isect = Intersect(Target, TargetRange) If Not isect Is Nothing Then Columns("C:D").Font.Size = 11 Target.Font.Size = 15 Exit Sub End If Columns("C:D").Font.Size = 11 End Sub
The problem is when i press ctrl+c to copy a cell content, so once i move the curser to the distenation cell then the copying mode in selected cell disappeared. It is because when the event is trigered then the copying mode stops. The question is : how to let the copying mode works even when the event is trigered ?
I need for it do perform the same action on multiple rows/ cells of data. What adjustment to the code needs to be performed to do so?
Sub fconcat() Range("C1").Value = Range("A1").Value & Range("B1").Value For i = 1 To Range("A1").Characters.Count Range("C1").Characters(i, 1).Font.Name = Range("A1").Characters(i, 1).Font.Name Next For i = 1 To Range("B1").Characters.Count Range("C1").Characters(Range("A1").Characters.Count + i, 1).Font.Name = Range("B1").Characters(i, 1).Font.Name Next End Sub
I'm trying to create a macro that allows users to hide or unhide selected rows, either via toggle or userform. The macro I have looks like this, but for some reason it doesn't hide the rows:
Sub Hide_Range() Dim UserRange As Range DefaultRange = Selection.Address Set UserRange = Application.InputBox _ (Prompt:="Select Range to Hide:", _ Title:="Hide Range", _ Default:=DefaultRange, _ Type:=8) Rows.Select Selection.EntireRow.Hidden = True End Sub
what's wrong with my code and how I can further develop it to let the user decide whether to hide or unhide the selected rows?
I have several workbooks, and the workbooks can include several sheets. Is there's an easy way to create a macro so the user selection to be hidden or unhidden is hidden or unhidden in all sheets in the workbook. E.g. if the user selection is to hide rows 54-189, the macro hides rows 54-189 in all sheets in the workbook.
I have this code as seen below that sends an email.
I would like to change the font size of - mymail.body=
How can I modify this existing code to achieve that.
Code:
Set myOlApp = CreateObject("Outlook.Application") Set mymail = myOlApp.CreateItem(olMailItem) mymail.Subject = strSubject mymail.Body = "WHAT DO YOU PUT INTO A BOX ?" mymail.Display mymail.ReadReceiptRequested = False mymail.attachments.Add "P:SR.xls" mymail.to = "email@email.com" mymail.Send
I have this macro that works perfectly. My boss wanted the subtotal lines within the report to be in a smaller font, so I added lines to the macro to do that. It won't work with those lines in there! It says the reference is not valid on the red line in the code below. The 2 blue sections are the ones I added to change the font size.
Code: 'Add subtotals at the end of each age bucket For i = 10 To LR Step 1 fr = Range("E" & i - 3).End(xlUp).Row If Range("B" & i).Value = "Totals" Then Range("E" & i).Formula = "=SUBTOTAL(9,E" & fr & ":E" & i - 2 & ")" With Range("E" & i & ":R" & i).FillRight
[code].....
Why would the font size make a difference? Is there some other way to change the font size that would work better?
I am trying to set up conditional formatting in cell C4 to change the font to a smaller number based on the value of C4. If C4 = 0 then Font Calibri 16, otherwise Font Calibri 24. However, the font size is not availabe, it is greyed out. Is there another way to accomplish this without using VBA? In the worksheet could I use an if() statement to change the font size?
I've come across a spreadsheet where certain rows and columns (typically top rows or left columns) are hidden; however, there's no way to unhide them (the unhide function is grayed out) and it doesn't seem to be protected or no visible macros/vba on the file.
I have a spreadsheet that has 28 columns for time entries. Typically only the first 12 columns are used, so I would like to hide the remaining 16 columns (which makes the spreadsheet much more user-friendly). It would probably be nearly impossible to teach all of them how to Unhide the remaining columns (and re-Hide), plus I would like to use the full-screen function when employees enter thier times. I would like to use a form control in the column heading so that when the employees 'check' it, it will Unhide and then re-Hide the columns. Any way to do this? Seems like a VB thing to me (out of my league, but would be happy to add one in!).
I once saw an excel sheet where I could hide or unhide a section by some + and - signes above the column-letters.... I have searched for this but I only get the ordinary hide/unhide solutions.
I'm looking to make a simple button that would hide a given range of columns.
This is the simplest I could find:
VB: Sub button1() Columns("AD:AE").EntireColumn.Hidden = Not Columns("AD:AE").EntireColumn.Hidden End Sub
Although this works nicely, there was another way to do it (looks more ergonomic and doesn't take up spreadsheet space). Here's a screenshot of what I mean: ColumnHide.gif
I have a single button I want to use to call a macro to: 1.Hide columns C:AZ if they arent already hidden 2.Unhide columns C:AZ if they are already hidden.
The code below if I run once Hide the Range("H:FV,GG:IV") and shows the Range("A:G, FW:GF") And if I run it again Unhide the Range("H:FV,GG:IV") And Show all columns
Code: Sub Hide_Unhide()Range("H:FV,GG:IV").Select If Selection.EntireColumn.Hidden = True Then Range("H:FV,GG:IV").EntireColumn.Hidden = False Else Range("H:FV,GG:IV").Select If Selection.EntireColumn.Hidden = False Then Range("H:FV,GG:IV").EntireColumn.Hidden = True End If End If Range("A1").Select End Sub
Above code is working with 2 cases now is it possible to add 3rd case Hide Or Unhide Range("H:GG,GI:HJ,HO:IV") in the same code, and shows the Range("A:G, GH, HK:HN")
Resume: my request Step1-if I run macro First time it must Hide Range("H:FV,GG:IV") and shows the Range("A:G, FW:GF") Step2-If I run macro Second time it must Hide Range("H:GG,GI:HJ,HO:IV") and shows the Range("A:G, GH, HK:HN") Step3-if I run macro third time it must show all columns
And repeat same cycle all time Step 1 to 2, Step2 to 3 and Step3 to 1
I have a worksheet with a fair amount of data. It is split into two parts: the main part is a table with data in columns D to AR while the other part is simply a list with checkboxes against each item. I have set things up so that when a checkbox is ticked against an item in the list, then a 2 appears in row 2 above the relevant column in the main table; if the checkbox is unticked then a 2 appears above the relevant column.
What I seek is a macro that will hide all columns that do not have the associated item ticked; in other words, I want columns to be hidden if there is a 1 in the relevant cell in Row 2 (and visible if there is a 2 there).
I have used the following code (obtained from this forum), but it doesn't work as expected. When I select the items, the cells in Row 2 react as expected, but the hiding and unhiding only occurs when I go to another Worksheet and then return to the Worksheet where the data is. Obviously I want the macro to work immediately I tick or untick a checkbox. What is wrong with the code I have used?
Private Sub Worksheet_Activate() Call HideCols End SubSub
I have a range of columns ( J:AH) which i have to hide from the user interface.In order to unhide, the editor must enter a password. He/ She can only enter the password thrice. After 3 times, if it is still wrong, the whole workbook must be closed. Is this possible? If not, I'll settle for something less complicated.
I need hide/show some column by using Macro Button. I have attached the excel sheet( name VBA testing.xls). I need to hide column K,L,N,O & visible column G,H by clicking button "Plan A".Similarly i need to hide the column G,H,N,O & unhide the column K,L by clicking the button "Plant 2. Similarly by clicking the Button "Plant 3", hiding the column G,H,K,L are needed whereas column N,O will be unhide.
