We took my parents that had limited mobility to
castaway cay with their wheelchairs. When we had a cabana, we just used their travel wheelchairs as we were only moving on paved paths until we were at the cabana. If we needed a sand wheelchair (we didn't, my parents were able to walk the short distance to the water from the cabana) the cabana host offered to have one/two waiting for us.
When we didn't have a cabana, we did leave the ship with their travel wheelchairs and did use the sand wheelchairs on the beach themselves. If you never used a beach wheelchair, they do allow you to move on the sand - just not easily! There seem to be plenty available, I know we didn't have a problem getting two of them when we needed them.
Some things I've learned - if you are leaving the ship at a busy time and there is a crowd for the tram, it might be better to skip the tram. It takes about the same amount of time to take the path to the main part of Castaway Cay as it does to take the tram. Each tram has one area for wheelchairs and if there is any crowd, it is difficult to get around them. If there is another wheelchair party waiting, you'll probably have to wait for the next tram.
If you use the sand wheelchair, choose your beach location with thought. There are parts of the family beach where the beach part is wider, some where it is more narrow - so depending on how close to the water you are intending to be more or less sand to cross to get from paved path to water. Also some parts that have more chairs/umbrella obstacles than others. So really look at the map of Castaway Cay to see where you want to be and send a scout ahead if you can.