Microsoft Excel VBA Questions

[Nytmare]

I know that there has to be at least one other person in here who has a working knowledge of coding visual basic in Excel.

First and foremost, I want to know who you are so that we can bounce problems off of each other while we use this god awful program for our character sheets.

Secondly, I need to solve this problem, and I don't even know how to word it properly for a google search.

What I know how to do: I push a button, and the program grabs a bunch of information from a bunch of cells and builds a comment box. That part is fine. The problem: I have 80 other instances where I want to use this program, but I don't want to program 80 different buttons for each different set of cells.

My solution is that each button has a different ID as it's name and the program looks at a different spot in the matrix based off of what button was pushed. I just have absolutely no idea how vba interacts with button names, or what to search for to find out.

 

[Cyberknight]

I haven't really done any VBA programming the last few years, but it's kinda like riding a bike.
I'm not really sure what you are trying to do with 80 buttons, but maybe this will help a little.

It's a good idea to name your buttons and if you have multiple buttons doing the same thing, adding an incremental number at the end of the name can be useful later on. If you want to do some loops with the buttons, like changing colors or text.

If you double click the button in edit mode it will automatically add a button click event. You can always copy and paste the same code in all 80 buttons and change the code for your specific button.

Also, with VBA if you add a period after the control name it will list all of the properties that you can use with that control. ie I named my button btnTest1 I typed btnTest1. and I got a list of about 50 properties associated with command buttons.

Look at 'Click event' in the help.

VBA can do alot, some things might be clunky, but overall it is pretty powerful.

 

[Nytmare]

Yeah, I figured out a working solution. I had been using Form Buttons before, and couldn't figure out how to wring any individual control out of them. Switching things over to Command Buttons gave me everything I needed though.

The 80 buttons are so that I can make a Rich Text copy of a monster in Adventure Tools, click a button in Excel, and build encounters (up to 4, 20 enemy encounters) in the ever growing coral reef of a DMs tool I use.

 

[Nytmare]

New question:

I build a .comment out of an Adventure Tools monster.

I want the program to look at the .comment, and bold every line that is a power. (basically any line that starts with m, r, c, or a followed by a space)

I know roughly what steps I want the program to take, but I have next to no experience dealing with string manipulation.

Anyone out there who can help?

 

[RedAnt84]

I wouldn't say I have "working knowledge" of VBA, but I've coded a couple of personal projects in it. Currently I'm working on a parsing/printing Java program that involves 4e statblocks.

The main function you're looking for is StrComp(string1, string2). You have to match the strings exactly. It returns 0 if they equal, or -1/1 if they're not (depends on if one comes after/before the other)

Basically, you'll want to grab the powers, split them by the newline character (vbCrLf), run through each power, and check to see if it starts with a power type. Then you'll bold that line in the comments. This is my first time working with comments, so hope it works.

References:

Check if a String Starts or Ends With a Given Set of Characters
Excel Comments VBA (for the comments and bolding part)

Here's my test code. Enter 'a', 'r', or 'm' to get a bold line:

Dim cmt As Comment
    Dim startsWith As Boolean
    Dim toCompare As String
    Dim originalString As String
    
    originalString = "a Power" & vbCrLf & "r Power" & vbCrLf & "m Power"

    toCompare = InputBox("Compare your text to: " & vbCrLf & originalString)

    Dim powerSplit() As String
    powerSplit = Split(originalString, vbCrLf)
    Dim power As Variant
    
    Set cmt = ActiveCell.Comment
        If cmt Is Nothing Then
            ActiveCell.AddComment _
            Text:=originalString
        Set cmt = ActiveCell.Comment
    End If
    
    For Each power In powerSplit
        startsWith = False
        If (Len(toCompare) <= Len(power)) Then
           startsWith = StrComp(toCompare, Left(power, Len(toCompare))) = 0
           'format the lines of text
           If (startsWith) Then
                cmt.Shape.TextFrame.Characters(InStr(1, originalString, power), Len(power)).Font.Bold = True
            End If
        End If
        
        
    Next power

Instead of the InputBox, you'll check to see if each power starts with an a, c, m, or r, followed by a space

I don't have Monster Tools available, but the things I've had to watch out for concerning powers (from pdfs) are: capital/lower cases, power names not getting correctly copied over ("aPower" instead of "a Power"), and when power types equal "none" (e.g. Goblin Tactics)

Hope this helps. First comment, ho!

 

[Nytmare]

Here's what I ended up doing. It's sloppy as hell, but it should make sense.

lilspace = " "
lilm = "m "
bigM = "M "
lilr = "r "
bigR = "R "
lila = "a "
bigA = "A "
lilc = "c "
bigC = "C "

Immune = "Immune "
Resist = "Resist "
Vulnerable = "Vulnerable "
ActionPoints = "Action Points "
Saves = "Saving Throws "
Aura = "aura "


Dim boldStuff As Variant
Dim colorStuff As Variant
boldStuff = Array(lilspace, lilm, lilr, lila, lilc, bigM, bigR, bigA, bigC)
colorStuff = Array(Immune, Resist, Vulnerable, ActionPoints, Saves, Aura)

For boldArray = 0 To 8
boldSearch = InStr(1, BadguyComment, Chr(10) & boldStuff(boldArray))

    Do While boldSearch <> 0
        ActiveCell.Comment.Shape.TextFrame.Characters(boldSearch, InStr(boldSearch + 1, BadguyComment, Chr(10)) - boldSearch).Font.Bold = True
        boldSearch = InStr(boldSearch + 1, BadguyComment, Chr(10) & boldStuff(boldArray))
    Loop
Next

For colorArray = 0 To 5
colorSearch = InStr(1, BadguyComment, colorStuff(colorArray))

    Do While colorSearch <> 0
        With ActiveCell.Comment.Shape.TextFrame.Characters(colorSearch, Len(colorStuff(colorArray))).Font
            .ColorIndex = 5
            .Bold = True
        End With
        colorSearch = InStr(colorSearch + 1, BadguyComment, Chr(10) & colorStuff(colorArray))
    Loop
Next

The basic steps are:

• Identify all of the patterns to look for
• Line up which patterns to feed through the bold process, and which ones to feed through the color change process
• Look for a pattern to bold (line break followed immediately by the pattern), take it all the way till you hit another line break and bold the entire line. Repeat till you run out of text.
• Look for a phrase to run through a color change. Bold it and color it blue. Repeat till you run out of text.