• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Getting control in my main macro code when a shape is selected|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Getting control in my main macro code when a shape is selected|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosGetting control in my main macro co…
sp_PrintTopic sp_TopicIcon
Getting control in my main macro code when a shape is selected
Avatar
Nels Anderson
Member
Members
Level 0
Forum Posts: 8
Member Since:
February 26, 2022
sp_UserOfflineSmall Offline
1
April 1, 2022 - 8:06 am
sp_Permalink sp_Print
  1. How do I get control in my main macro code when a shape is selected by the mouse left button? Currently, control appears to only be given to a subroutine via the OnAction Currently I achieve control by writing a disk file in the subroutine and monitoring for the file’s existence in a loop in the main macro code.
sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
April 2, 2022 - 3:47 pm
sp_Permalink sp_Print

Hi Nels,

Can we see that main macro code? What do you mean by achieve control? You want to pause the macro while you do manual things and you want the code to resume when you're done?

Avatar
Nels Anderson
Member
Members
Level 0
Forum Posts: 8
Member Since:
February 26, 2022
sp_UserOfflineSmall Offline
3
April 6, 2022 - 9:31 am
sp_Permalink sp_Print

Mr Bombea:

I'm attempting to make a "Wordle" like game (named Fourdle) that is played in an Excel worksheet.

It currently only uses 4 letter words.  My intent is to make a tool used by elementary level school teachers which allows them to create 4 letter words and their definitions.  Where the definitions are partially revealed as the student gets more of the letters in his guess correct.

VBA does not appear to me to have the ability to do something like stop_execution.OnAction = "GoTo anylabel", instead, the OnAction requires me to reference a subroutine.  I have found a way around this by 1. Deleting a fixed named file (Signal) if it exists on macro startup, then when the stop_execution button (Shape) is selected by the user, writing the "Signal" file to the hard drive, while constantly querying for the file's existence in the infinite loop.  When it is detected, then I write the current Fourdle game state to a "history" file and terminate the macro execution.  This workaround has the disadvantage of only working while a single user is playing the game due to not having a different signal file name for each player.

I am in an infinite loop in the fourdle macro code

Sub fourdle()
Dim version As String 'Macro revision version number.
Dim number_of_worksheets As Integer 'Total number of sheets in this workbook.

...

'Create path to signal file in the current working directory.
signal_file_name = ThisWorkbook.Path & "\" & "Signal"

'If the signal file exists on game startup, then delete it.
If Len(Dir(signal_file_name)) > 0 Then
Kill signal_file_name
End If

...

'Create the game stop button.
'Set stop_execution = Worksheets(game_sheet_pos).Shapes.AddShape(msoShapeRectangle, 20, 20, 72, 72)
sh_left = Cells(2, 10).Left
sh_top = Cells(2, 10).Top
sh_width = Cells(2, 10).Width
sh_height = Cells(2, 10).Height
Set stop_execution = ActiveSheet.Shapes.AddShape(msoShapeRectangle, sh_left, sh_top, sh_width, sh_height)
stop_execution.Fill.ForeColor.RGB = colorx(cyanx)
stop_execution.OnAction = "StopAndSave"
stop_execution.TextEffect.Text = "STOP & SAVE"

'The game setup and display has been established now so start the infinite loop which monitors for word guess input.

Check_For_New_Input:
While (1)

...

'The player enters characters in cells while this loop is executing

...

Waiting_For_All_Four:
start_time = Timer
DoEvents
Application.Wait (Now + TimeValue("00:00:03"))
end_time = Timer
'MsgBox ("Event delay = " & (finish - Start))
wait_time = end_time - start_time
If Len(Dir(signal_file_name)) > 0 Then
GoTo StopAndSave1
End If

x = x 'Breakpoint place holder.
Wend

StopAndSave1:
'MsgBox ("GAME TERMINATED BY USER")

'Change the stop box to red to indicate the game is stopping.
stop_execution.Fill.ForeColor.RGB = colorx(redx)
DoEvents

'The logic to write the current game history to a disk file with a name unique to the player has yet to be written, but goes here.
x = x 'Breakpoint
GoTo Macro_Exit

'This subroutine gets called when the player presses the "Stop & Save" button.

Sub StopAndSave()
Dim signal_file_name As String
Dim output_file_number As Long

signal_file_name = ThisWorkbook.Path & "\" & "Signal"

If Len(Dir(signal_file_name)) <= 0 Then
'Signal file does not exist.

'Get the next available file number.
output_file_number = FreeFile() 'Open the raw output file.
Open signal_file_name For Output As output_file_number Len = 8192
Write #output_file_number, "Signal"
Close #output_file_number
Else
'Ignore multiple requests to quit.
End If
x = x 'Breakpoint
End Sub

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
April 6, 2022 - 3:43 pm
sp_Permalink sp_Print

This workaround has the disadvantage of only working while a single user is playing the game due to not having a different signal file name for each player.

That can be easily fixed:

signal_file_name = ThisWorkbook.Path & "\" & "Signal"

signal_file_name = ThisWorkbook.Path & "\" & Environ("username")

using the username of the current user read from the system environment will handle multiple users.

sp_AnswersTopicAnswer
Answers Post
Avatar
Nels Anderson
Member
Members
Level 0
Forum Posts: 8
Member Since:
February 26, 2022
sp_UserOfflineSmall Offline
5
April 9, 2022 - 7:20 am
sp_Permalink sp_Print

Mr Bombea:

Thanks.

That response fixes the problem and will allow multiple users to use the

"Write to a file from a subroutine to communicate with the main module" technique.

Nels

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ayal Telem, Mark Stevens
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27292

 

Member Stats:
Guest Posters: 49
Members: 31913
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.