Re: Run Macro On Value Being Entered
Hi John,
Sorry, I have been away on holiday. I hope Dave's file has helped you?
Regards,
Averil
Re: Run Macro On Value Being Entered
Hi John,
Sorry, I have been away on holiday. I hope Dave's file has helped you?
Regards,
Averil
Re: Automatically Tick Cell If Entered Value Matches
Sorry, I have been in NZ on holiday and didn't have a chance to check my emails...
Hi rbrhodes,
Thanks for the heads up on using Target.Value - it is added to my growing list of VBA gems from this forum :thanx:
Cheers,
Averil
Re: Disable Some Right Mouse Click Options
Hi there,
Just type in the name of the control as it appears in the right click menu (i.e. include the "...")
With Application.CommandBars("Cell")
.Controls("Cut").Visible = False
.Controls("Copy").Visible = False
.Controls("Paste").Visible = False
.Controls("Paste Special...").Visible = False
.Controls("Insert...").Visible = False
.Controls("Delete...").Visible = False
.Controls("Clear Contents").Visible = False
'etc
'etc
'etc
End with
Display More
p.s. End code tag is [/CODE] not [CODE/] :smile:
Cheers,
Averil
Re: Automatically Calculate Based On Another Cell
Just out of curiosity, why don't you want a formula in the cell?
Re: Automatically Calculate Based On Another Cell
Hey there,
Sorry, I haven't really had a chance to think about this. Probably out of my league now anyway :? ... thought I'd try my hand at helping out in the forum I get so much out of - guess that didn't work :oops:
Hopefully someone else can help you troubleshoot your code?
Cheers,
Averil
Re: Calculate Cell Value Based On Other Cell Automatically
Kia Ora,
Check out these instructions for using the worksheet_change event.
Cheers,
Averil[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi again,
I've just had a quick chance to look at your spreadsheet. I'm still mostly a learner when it comes to VBA, but this works as long as you have formatted the other cells the same as you have the green ones (e.g. font or else you will just get an "A" in the column).
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Select Case Target.Value
Case Is = "Car"
Target.Offset(0, 1).Value = "a"
Case Is = "Truck"
Target.Offset(0, 2).Value = "a"
Case Is = "Motorbike"
Target.Offset(0, 3).Value = "a"
Case Is = "Hovercraft"
Target.Offset(0, 4).Value = "a"
Case Is = "Plane"
Target.Offset(0, 5).Value = "a"
Case Is = "Ship"
Target.Offset(0, 6).Value = "a"
Case Is = "Moped"
Target.Offset(0, 7).Value = "a"
Case Is = "Bicycle"
Target.Offset(0, 8).Value = "a"
Case Else
MsgBox ("Oops, that doesn't seem to be an option")
End Select
End If
End Sub
Display More
You may want to look at having data validation for where the user enters the vehicle type since the select case seems to be case sensitive (???)
Cheers,
Averil
Re: Run Macro On Certain Value Being Entered In Cell
Kia ora!
Oops, didn't realise I'd forgotten to turn it back on... thanks for picking that up!
Cheers,
Averil
Re: Run Macro On Certain Value Being Entered In Cell
Thanks Dave - I never knew about the BorderAround method!
John,
I have tested this on my computer and seems to work fine. I have this code in a module:
Sub Hidemacro()
Application.ScreenUpdating = False
With Worksheets("Events")
.Unprotect
.Range("D3:AF3").BorderAround (xlContinuous)
.Columns("AF:AF").EntireColumn.Hidden = True
End With
End Sub
Sub UnHidemacro()
Application.ScreenUpdating = False
With Worksheets("Events")
.Unprotect
.Range("D3:AF3").BorderAround (xlContinuous)
.Columns("AF:AF").EntireColumn.Hidden = False
End With
End Sub
Display More
And the earlier code in the "Charity Workers" sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case ActiveSheet.Range("B2").Value
Case Is = "Yes"
Call Hidemacro
Case Is = "No"
Call UnHidemacro
Case Is = ""
Call UnHidemacro
End Select
End Sub
Display More
If you like, I can post the file I have been working on.
Cheers,
Averil
Re: Macro To Run On Cell Answer
Hi there,
Put something like this in the worksheet code itself (not in a module). The code will run and call your macros depending on what the value is in B2
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case ActiveSheet.Range("B2").Value
Case Is = "Yes"
Call HideMacro
Case Is = "No"
Call UnhideMacro
Case Is = ""
Call UnhideMacro
End Select
End Sub
Display More
Regards,
Averil
Re: Acrobat PDF Print Preview Not WYSIWYG
What I didn't understand is why the print preview was fine but the PDF was different (the second time it also did the same thing even with Acrobat Distiller selected as the printer). As I had a deadline, my workaround was to indent the cell alignment of the columns which seemed to make it a little better.
AP
Re: Acrobat PDF Print Preview Not WYSIWYG
Thanks Dave for your reply, and modifying my title more appropriately.
I use the term "PDFing" when creating a PDF, i.e. "printing" using Acrobat Distiller will create a PDF file.
The cells to the right aren't blank. Screenshot 1 attached is a screenshot of part of the Excel document. Screenshot 2 is a screenshot of the same area of the PDF document.
I appreciate your help here.
Regards,
Averil
Good afternoon,
I apologise if my title is not very helpful. I cannot think how else to describe my question.
I have a spreadsheet which needs to be scaled down when printing (to fit 1 page wide by x tall), everything looks good in the print preview, but when PDFing, some of the columns don't seem to wrap properly and some of the words on the right side blend into the next column.
After playing around with it, if I set the column to autofit it slightly expands the column width and works correctly. Unfortunately, this will make the text too small to read by the time it is scaled down for PDFing so I have left the columns as they were.
I had a feeling that this difference between what was on screen and the end result could be due to the "printer" settings. So, because the default printer was not Acrobat, I changed the printer to Acrobat before attempting to print preview and print again. Yippee, it worked.
I was just wondering if anyone could explain phenomenon this to me?
Regards,
Averil
Re: Survey Results In Pivot Table
Hi Dave,
Thanks for looking at my question.
I had thought to keep the top level questions grouped with the possible answers. I'm not too cluey with pivot tables so maybe I just don't understand the extent of what they can/can't do.
If I try and add Busy, Stressed, Tired etc. to the row area of my pivot table Excel seems to want to add it to the right of the existing one, and not below it. Is there a way that Excel can do a Pivot table like the example thrown up in the "data" spreadsheet?
Regards,
Averil
Hi all
I have just recently finished inputting data into a spreadsheet which is a whole bunch of survey responses and I am trying to figure out the best way of now “analysing” it. Unfortunately the questionnaire consists of various types of questions, including some open ended, some where the participant selected a number on a scale, or the participants could select any number of options (e.g. tick any that apply).
From a brief search, I see that you can’t have two headings as such for a pivot table so I am wondering what the best approach might be. I have attached a sample spreadsheet with some of the questions if it helps. I have a hunch though that it’s going to be a matter of analysing each question individually and using filters and countif formulas (see attached).
Your input would be appreciated before I jump in and start doing everything manually :smile: .
Thanks
Averil
Re: Enter Values Offset From Current Selection
Thanks jindon and Mavyak,
I have used your suggestion jindon, and it works perfectly.
Thank you so much,
Averil
Good afternoon,
I am sure that this is something simple but I have tried searching the forum but did not find quite what I am looking for.
Is there a way that values can be entered into cells offset from the currently selected range? I know I can have the following work when there is only one cell selected:
However, I would like to be able to put 1 in the cells one row down for each cell selected, for example if the current selection is $D$6:$F$6,$I$6, then $D$7:$F$7,$I$7 will contain the number 1 once the code has run.
Please let me know if I can clarify anything for you.
Thanks in advance,
Averil