Order Of Executed Code - Synching Sounds With Making Shapes Visible

Jul 8, 2014

I was working on a presentation for work where I wanted to build a 'Family Feud' type board to play a game. In the board I am using activex text boxes with code that would hide the text box to reveal the answer underneath or it would show a custom shape (an X in a box) and play the buzzer sound. The code is pretty simple, first I would make the shape visible, then I would play the sound then the shape would be made invisible. If I step through the macro everything works fine, but when I run the macro, you never see the shape. It's almost as if the sound plays before the shape shows up and then it is made invisible again. I tried putting a wait and a sleep command between making the shape visible and playing the sound but that made no difference. If I remove the code to hide the shape at the end and run the macro, the sound plays and then the shape appears. Is there anyway to have the shape appear prior to or at the same time as the sound plays?

Code:
Private Sub CommandButton8_Click()
Application.ScreenUpdating = True
ActiveSheet.Shapes("First Strike").Visible = True
Play_Strike_Sound
'The previous line refers to another macro that has the code commented below. The sndPlaySound32
'function is one I picked up from cpearson.com
'sndPlaySound32 "C:\_Fin SysSoundsff-strike.wav", SND_SYNC
ActiveSheet.Shapes("First Strike").Visible = False
End Sub

View 3 Replies


ADVERTISEMENT

Test Whether Code Has Executed

Mar 22, 2008

I have some code which formats a worksheet based on user selections in a form. The code works, except when the user has made the same selections in two multi-select list boxes. I need to find a way to determine whether the code has already formatted the worksheet.

To me, it seems that there might be two ways to do this, 1) have a test on the original block of code that checks whether this portion of the code has already executed for this variable or 2) check the worksheet for the string (the code formats the same string each time but adds the variable for the current iteration of the loop only if Year1 = True.) The problem is that I only want to format the column if it hasn't already been formatted for that selection.

If CostShare = True Then
For Each ctrl In UserForm2.Controls
If TypeName(ctrl) = " ComboBox" And ctrl.Visible = True Then
If ctrl.Value = "Cost-Share" Then
t = ctrl.Tag
For Each ctrl2 In UserForm2.Controls
If TypeName(ctrl2) = "ListBox" Then
If ctrl2.Tag = t Then
With ctrl2
For ii = 0 To .ListCount - 1...............................

View 2 Replies View Related

DDB Fixed Upon Making It Visible Without Having To Scroll Up And Down?

Mar 26, 2012

I have a Drop Down Box that I am hiding upon selection of another DDB. I can get it to be hide/show using the following code:

ActiveSheet.Shapes("Drop Down 4").Visible = False

and

ActiveSheet.Shapes("Drop Down 4").Visible = True

The only problem is, that when it becomes visible again, the DDB appears in a different location about an inch to the right of where I orginally had it. However when I scroll using my mouse so the DDB is not showing on my screen, and scroll back up, the DDB jumps back into correct position, with the correct size.

Is there anyway to keep the DDB fixed upon making it visible, without having to scroll up and down? As it will look really bad to my end user.

View 5 Replies View Related

Show Progress Of Code Executed In Excel Sheet?

May 31, 2013

i want to know how to show the progress of code exceuted in an excel sheet. like when we download a file from web or server it shows the staus of the file transfer.

View 2 Replies View Related

Code To Be Executed And Then Have The Spreadsheet Save Itself And Close Definitively

Oct 22, 2009

When I close my worksheet excel asks me if I wish to save the file one last time, if I click 'yes' I would like some more code to be executed and then have the spreadsheet save itself and close definitively, how can this be done using vba?

View 9 Replies View Related

Show User Form After Workbook_open Code Has Executed

Apr 19, 2006

i have this code which askes the user for a job number once the workbook is opened.

Private Sub Workbook_open()

' If sheet was named by original open routine, exit

If ActiveSheet.Name = "Main Roof" Then Exit Sub

' otherwise

Do
Returnvalue = InputBox("Please Enter a New Job Number.", "Information")

' Allow changes by entering q as the Job Number
If Returnvalue = "q" Then Exit Sub


' Delete the ' from the front of the following two lines and
' then when you enter q as the Job Number you will also be
' asked for a password. The default password is toe.

what iam trying to do is get it to open the userform "WorkSelection" after it has completed the above code.

View 3 Replies View Related

Shape Locking (original RAG Shapes In Order To Replicate The Feature In Other Sheets)

Feb 17, 2010

how to setup the original RAG shapes in order to replicate the feature in other s'sheets.

If you drag away from one of the RAG shapes, it takes a copy of the shape but leaves the original shape intact. It seems to be locked in place, but there are no macros or VBA code doing it.

View 2 Replies View Related

VBA Loop For Making Command Buttons Visible

Jun 10, 2013

I'm trying to write a code to make a number of buttons visible depending on a cell value

I have 10 command buttons all are invisible and I want to show only the first x
x is the value of cell "A1" in "Sheet1" (will be from 1 to 10)

Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim i As Long
For i = 1 To Sheet1.Range("A1").Value
CommandButton& i.Visible = True
Next i
End Sub

View 1 Replies View Related

Code Breaks Sometimes/sometimes Not: Subroutine Executed When The User Presses A Command Button

Mar 10, 2007

I have inherited support for the an Excel 'program' that seems to be fairly unstable. I am currently having problems with the following code in a subroutine (executed when the user presses a command button):

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$7"
.PrintTitleColumns = ""
.PrintArea = ActiveSheet.UsedRange
End With

Sometimes it works and sometimes I get the following error: Run-time error '1004'
Unable to set the PrintArea property of the PageSetup class.

… on the .PrintArea = ActiveSheet.UsedRange

I am having a hard time figuring out what is causing its wishy-washiness. Running the following code (launched from worksheet named 'consumer' with a command button):


Sub subConsumerFacesheet()...................

View 3 Replies View Related

Making One Sheet Visible Once A Week Or When Command Button Is Pressed

Nov 10, 2008

I have a workbook with about 53 worksheets one for every week in the year. They are named as follows:
VA-1-1-09 TO VA 12-28-09 All sheets are hidden except the (VA-1-1-09)

What I am trying to do is create some logic that would keep all 52 sheets Very hidden except sheet VA-1-1-09 Till the second week starts at that time what I would like to see happen sheet VA-1-5-09 come out of very hidden and appear visible and send sheet VA-1-1-09 to just a hidden state. I like to have this happen for every week of the year? If this can happen automatically that would be great however a command button clicked once a week would do the trick as well.

View 4 Replies View Related

Making A Command Button Visible Based On Data In Cell

Oct 15, 2007

I have a data validation list in cell D11 on sheet "Data Entry" and a command button "btnMultipleProperties" that I only want visible if "Multiple" is selected in "D11" I have the below code in "This Workbook" in VBE but it doesn't work. What did I miss?

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Data Entry")
If [D11].Value "Multiple" Then
btnMultipleProperties.Visible = False
Else: btnMultipleProperties.Visible = True
End If
End With
End Sub

View 8 Replies View Related

Playing 2 Sounds One After The Other

Apr 14, 2014

I have the following sub for me to play sound files:

[Code] .....

Essentially what i want to do is play one sound file, then immediately play another. I am using two subs so that can be achieved as when I was using one sub the first sound didn't play as it was immediately replaced by the second sound.

The lines of code where I call my sounds are:

[Code] .....

I thought the addition of:

[Code] .....

Would allow the first sound to play, before then playing the second sound, however this doesn't seem to be the case and the second sound doesn't play at all.

View 8 Replies View Related

Copy Visible Rows And Paste To Visible Rows In Same Sheet Of Excel / VBA Code

Oct 27, 2013

I need to write a macro where i need to copy set of rows from few columns of an excel sheet to another set of columns in same sheet . My excel looks something like this...

Product
F1020
F1023
F1025
F1120
F1123
F1125

[code].....

Now when i filter this table for Product PR01 only rows 1,3,4 will be visible while the other rows remains hidden

I WANT TO COPY ROWS COMING UNDER COLUMNS

F1120
F1123
F1125

TO

F1020
F1023
F1025

when i use the code

Selection.SpecialCells(xlCellTypeVisible).Copy

i get to select ones those are visible but i am not sure how i can PASTE them to rows visible under column f1020 to f1025

Tried this in a frantic effort

Selection.SpecialCells(xlCellTypeVisible).PasteSpecial xlValues

But got an error for " multiple selection"

View 1 Replies View Related

Add Shapes To UserForm Via Code

Nov 25, 2006

How do i draw shapes on the fly on forms?

View 4 Replies View Related

Code To Delete Shapes But Not Charts

Nov 25, 2011

I have the following code:

For Each wShape In ActiveSheet.Shapes
wShape.Delete
Next wShape

This works great to delete the buttons and rectangles I have on my worksheet, but it is also deleting charts. Is there any way to avoid the chart deletion?

View 5 Replies View Related

VBA Code - Creating And Moving Shapes In Excel

Mar 9, 2014

1.) Create shapes (customize oval shape), I was able to create one but I think the code needs improvement to have the shape a fixed name. - see attached excel file.

2.) Move shapes into corresponding cell. Example: (Oval shape1, should be in cell g6). - see attached excel file.

Customize Heatmap creation.xlsm

View 1 Replies View Related

Creating Shapes (Rectangles And Shapes) From Input Cells?

Aug 30, 2013

Im trying to create shapes Rectangles and Squares with different colours within an excel sheet, where the length and height of the shapes is generated by input values.

Also is it possible to create 3D shapes, again where the size of the shap is generated by input cell values.

View 1 Replies View Related

Autosize Cell Comments/Shapes Via Macro Code

Aug 25, 2006

Among many attempts to insert and autosize a comment using VBA manually, which errored, i recorded the following bit. When running the macro, it does format the cell - text, not the comment - text and errors in the line highlighted in red.

Range("A14").Select
Range("A14").AddComment
Range("A14").Comment.Visible = False
Range("A14").Comment.Text Text:="Ladida"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With

I just want to add

.AutoSize = True

or something to that effect to the bit below. Any suggestions?

Range("A14").Comment.Text "Ladida"

View 5 Replies View Related

Making Code Efficient

Dec 29, 2009

following code be write down in more efficient manner :>>>

Range("B3") = Range("L" & Target.Row)
Range("C3") = Range("O" & Target.Row)
Range("D3") = Range("Q" & Target.Row)
Range("E3") = Range("R" & Target.Row)

View 9 Replies View Related

Making Code Available To Other Workbooks

Jul 13, 2006

I have written a sub that will compare the data in one colum to the data that i paste in another colum, when it finds a similarity it deletes the row. Everything works fine, but the file that needs to run the code will be changing every month. how do i export or package to use in other sheets. Also what command will i need to insert to get the new sheet to run the code.

View 7 Replies View Related

Converting Hex To Dec In VBA And Making Code Efficent

May 12, 2009

Wondering where this code can be adjusted to be much more efficient. Right now it is going through 1000 rows and 14 columns and it is taking about 10 minutes. I eventually need it to go through 6000 rows and 32 columns..

I already stored the HexToDec(Cells(x, 1)) result to variable H2D and it didn't really make too much of a noticeable difference.

View 14 Replies View Related

Making Code Run With Cancel Option?

Mar 26, 2012

I have a macro with a vbYesNo in it currently, I tried adding cancel as an option (vbYesNoCancel) but ran into some issues. aMaking my code run with the Cancel option? All I need is to have the cancel option there and have the sub end if cancel is selected.

Code:
Sub Sendit()
Dim lr As Long
' Conformation Box for Daily Sales Report Date[code]......

View 7 Replies View Related

Making Filepath Hyperlink In Email Generated With Code

Mar 14, 2014

I have the following code to do something similar to above but I need to add the filepath as a hyperlink to the email along with the body text. I have 4 cells in the Excel workbook that contain 1) Who to sen to:, 2)The Subject:, 3) A sentence for the Body of the email and 4) The filepath of the workbook as a hyperlink.

I want to add both the Body text and the hyperlink to the email so that the person opening the email can click on the link and open the file.

[Code] ....

View 10 Replies View Related

Making Existing Code Work For Specific Worksheet/s

May 30, 2013

I would like the following code to be run for all those sheet/s that has a name = "single" (Not case sensitive neither an exact match) of my active workbook.

Code:
Sub UIUIUI()
Dim LR As Long, i As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("I" & i)
If .Offset(, -1).Value = 1 Then .Value = .Value & "-"
End With
Next i
End Sub

View 3 Replies View Related

SumIF Only The Visible Using Vba Code

Oct 22, 2008

I was looking at the message board about what i want but i didnt find it as a whole
What i mean is, that i found the sum of the visible cell only or the sum of a condition
Anyway what i want is:

I hide some rows using a vba code and i want to sum what is left under condition

I have 5 columns , the 2 have the interest.

The column e5:e5000 have values

The column f5:f5000 have specific text --> ("On","Off","Other")

I want ,using VBA, to know, its time i run my the code, the sum of the visble cells under the "On","Off","Other" condition in 3 cells
eg. E1 will have the sum of the "On"
F1 will have the sum of the "Off"
G1 will have the sum of the "Other"

View 9 Replies View Related

VBA Code For Move To Next Visible Column?

Feb 26, 2014

Right now I am at Column D; From E to I columns are hidden in my worksheet and would like to move to Column J.

View 7 Replies View Related

Print Only Visible Sheets To PDF Code?

Feb 27, 2012

I have a workbook with many worksheets. All I want to do is print to PDF those that are visible (I can use just the code name number can't I?) and ignore those that are hidden. It seems simple enough but it will run and do nothing.

Dim sMsg As String, FName As Variant
Dim myArray() As Integer
Dim i As Integer
Dim j As Integer

[Code]....

View 6 Replies View Related

GIF Not Visible On UserForm While Code Runs

Aug 8, 2006

I have some code to put a GIF animation into a web browser on a userform. This works fine - on its own. However, once the GIF has loaded I want to run a whole bunch of code in the background. If I run the code all at once, the userform appears but the picture doest show - but when I place a breakpoint between loading the userform and the main sub routine of my code - the GIF loads and the animation is shown all the time my code is running in the backgorund.

View 4 Replies View Related

Order Into Quintiles Macro Code

May 4, 2008

I would like to know if ther is any quintile function or something like that. Is just that i need to order some imformation in quintile in visual basic.

View 3 Replies View Related

Rearrange The Order Tabs Display In Through VBA Code?

Jun 28, 2007

Is there any way to rearrange the order tabs display in through VBA Code?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved